Member Avatar for silverspring

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

  • 2 Contributors
  • 1 Reply
  • 111 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by campkev
Member Avatar for campkev

try this

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.