SQL select statement help

Expand Post »

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

Similar Threads

Hi,Need help on sql select statement structure in MS SQL
Question about SELECT statement in MySQL
Getting current value in Select Statement in MS SQL
cfselect issue trying to get results from a sql select statement in ColdFusion
Program Problem with a select statement to access Data base in C

silverspring

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Apr 2006

Permalink

Apr 26th, 2006

Re: SQL select statement help

try this

(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

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS Access and FileMaker Pro Forum Timeline: ACCESS Security

Next Thread in MS Access and FileMaker Pro Forum Timeline: my head hurts from this..

DaniWeb Message

Cancel Changes

User Name
Password