MS Access and FileMaker...

SQL select statement help

Please support our MS Access and FileMaker Pro advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2006

Posts: 1

Reputation: silverspring is an unknown quantity at this point

Solved Threads: 0

silverspring silverspring is offline

Offline

Newbie Poster

SQL select statement help

Apr 25th, 2006

Hi,
I have column with alphanumberic fields like:
trader1.dbpt
1234.mcxl
abc243.cexl
234-lab.cxcl
There are following characteristics of this data:
-fifth last place is always decimal
-always four characters after the decimal
What I want:
-only those rows which have ".mcxl" at the end
-rows should NOT have numbers in them
-rows should not have "-" in them
-want to sort according to the length, shortest should come first
Please guide how to accomplish all this on MS Access in SQL statement

thanks for the help,
Silverspring

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: SQL select statement help

Apr 26th, 2006

try this

Help with Code Tags

(Toggle Plain Text)

select * from tableName
where columnName like '%.mcxl' 
and columnName not like'%0%'
and columnName not like'%1%'
and columnName not like'%2%'
and columnName not like'%3%'
and columnName not like'%4%'
and columnName not like'%5%'
and columnName not like'%6%'
and columnName not like'%7%'
and columnName not like'%8%'
and columnName not like'%9%'
and columnName not like'%-%'
order by len(columnName) asc