Search WITHIN a field - 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

General SQL Server Forums

New to SQL Server Programming

Search WITHIN a field

Author

Topic

saltcod
Starting Member

13 Posts

Posted - 2010-02-09 : 13:48:43

Hey folks,

I'm having a devil of a time trying to search within a field.

I've got data that looks something like this:


Age		Sex		Weight		Conditions
---		---		------		----------	
55		M		155		Asthma,heart disease, 
88		F		166		heart disease
32		M		205		Smoker, Asthma,heart disease, diabetes,
28		F		188		diabetes, smoker
33		F		201		severe allergies

And I only want to return ONLY those patients with Asthma listed as a condition Like this:


Age		Sex		Weight		Conditions
---		---		------		----------
55		M		155		Asthma, heart disease  
32		M		205		Asthma, heart disease, diabetes, smoker

I'm using MS Access ( I know, I know), and I thought something like '%Asthma%' would do the trick, but it doesn't.

Any suggestions?

thanks!

Terry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 13:50:25

not sure about Access. however below will work in SQL Server

SELECT * FROM YourTable WHERE ','+ Conditions + ',' LIKE '%,Asthma,%'

saltcod
Starting Member

13 Posts

Posted - 2010-02-09 : 13:55:57

Humm.....thanks

It doesn't generate an error, but also doesn't return any records.

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 13:56:36

I think in Access instead of

like '%Asthma%

it might be:

like '*Asthma*'

Note that Visakh's approach will match whole words correctly, so if the "*" is correct best to consider his method.

saltcod
Starting Member

13 Posts

Posted - 2010-02-09 : 14:01:11

Thanks to both -- the * does indeed make the difference.

A final question - how would I make it so that i can enter into a dialog box what I want to query for?

So I get something like this:
SELECT * FROM YourTable WHERE ','+ Conditions + ',' LIKE '*,--Enter the condition here--,*'

Thanks!

Terry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:02:24

do you mean this?

http://support.microsoft.com/kb/294954

saltcod
Starting Member

13 Posts

Posted - 2010-02-09 : 14:07:41

aah.....that's exactly what I was looking for actually.

Thanks a lot for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:10:54

welcome