Case sensitive LIKE searches

starstarstarstarstarstarstarstarstarstar Rating: 0/5 (0 vote cast) print

I am facing a problem writing a query.
The problem is I have a column with different formulas of chemical molecules. for eg CH3Cl
                  CbF-(Cb2)
                  CBF-(Ca)
Now I wrote this query
SELECT *from tbl where MolFormula LIKE 'Cb%'


This returns me row 2 and row 3 whereas I just need row 2 to be returned.  I am using MS Access. Thanks for looking into it.

 : abhishekmadas     Reply  

Replies (6)

SELECT MolFormuala

FROM Group_Table

where INSTR(1,MolFormula,'Cbf',0)<>0

This query returns all molecules containing 'Cbf' but making a case sensitive search. This is what the result set is

MolFormula

Cbf-(Cb,2Cbf)

Cbf-(3Cbf)

Cbf-(2Cb,Cbf)

Cbf-(Cbf,2Nb)

Cb-(C,Cbf,Cb)

CbBr-(Cbf,Cb)

CbCl-(Cbf,Cb)

CbH-(Cbf,Cb)

pretty good. But now I want only those that start with Cbf i.e. the first 4 rows.

I tried to further filter :

SELECT MolFormula FROM

(

SELECT MolFormula

FROM Group_Table

where INSTR(1,MolFormula,'Cbf',0)<>0

)

WHERE MolFormula LIKE 'Cbf%'

but I don't get any results. Where am I going wrong?


profile
KINGSLEY TAGBO

A small change to your SQL Query may get it to work. You may need to alias your inner 'Select' statement.

Try this and give me a feedback:

SELECT MolFormula FROM

(

SELECT MolFormula

FROM Group_Table

where INSTR(1,MolFormula,'Cbf',0)<>0

) AS S

WHERE MolFormula LIKE 'Cbf%'


nah I tried that. Itsgiving mean empty result set.


profile
KINGSLEY TAGBO

This should work:

SELECT MolFormula FROM

(

SELECT MolFormula

FROM Group_Table

where INSTR(1,MolFormula,'Cbf',0)<>0

) AS S

WHERE MolFormula LIKE 'Cbf*'


profile
KINGSLEY TAGBO

MsAccess may not support case-insensitive queries. You may have to compare the casing of the words returned in VBA or some other middle tiercode.

Will you be able to do that?


Hmm probably not. I need to filter results through a query itself. I am sure there must be a way.



Post A Reply

 Questions & Answers