case sensitive LIKE search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

case sensitive LIKE search

Author

Topic

abhishekmadas
Starting Member

19 Posts

Posted - 2005-04-27 : 12:38:44

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.

Kristen
Test

22859 Posts

Posted - 2005-04-27 : 13:45:49

SELECT * from tbl where MolFormula COLLATE Latin1_General_BIN LIKE 'Cb%'

Kristen

Kristen
Test

22859 Posts

Posted - 2005-04-27 : 13:46:35

Aggghhhh - is this Access? If so don't know, sorry.

Kristen

mfemenel
Professor Frink

1421 Posts

Posted - 2005-04-27 : 15:00:59

Got my nerdy side going with that one. Use the strComp function. I used a binary compare(last operator in the function) to make it case sensitive.
SELECT *
FROM tbl
WHERE strComp(MolFormula,'CBF',0)=0
The link below describes the strComp function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctstrcomp.asp

Mike
"oh, that monkey is going to pay"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-27 : 15:28:15

You can also use the INSTR() function, if you want to similate a LIKE a little better. The 4th argument, usually ommited, can be set to 0 which indicates to use a binary compare, just as in strcomp().

WHERE INSTR(1,MolFormula,"CB",0) <> 0

that should be the "case sensitive" equivalent to WHERE MolFormula LIKE "*CB*".

- Jeff

abhishekmadas
Starting Member

19 Posts

Posted - 2005-04-27 : 16:03:08

Thanks. You have probably gotten the answer for me. Am really close.

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?

abhishekmadas
Starting Member

19 Posts

Posted - 2005-04-27 : 16:05:16

sorry printing error:

WHERE MolFormula LIKE 'Cbf%'

No result set.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-27 : 16:51:46

what does INSTR return? Look up the function in Access VBA help. Can you figure out what you should change? I'll give you a clue -- you only want rows in which the INSTR() returns a value that tells you that the string begins with the characters you are looking for....

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources