Search field for part of a string

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 field for part of a string

Author

Topic

btscotland
Starting Member

2 Posts

Posted - 2008-09-04 : 17:26:19

Hi there. Please go easy and apologies if this has been answered elsewhere. I am not sure how to word a search and haven't really come up with anything.

Basically I am trying to perform a fairly loose search on a database. I have a table that stores details of properties with one field containing the town in which the property is.

Using ASP over the web I wish the user to enter a string of characters such as "Perth, Belfast, Bradford, Aberdeen".

I wish to query the database Town field for a match of this string. Obviously the database would only have one town for each record.

My initial thought was that it would simply be:
SELECT * from Properties where Town LIKE '% mysearchstring %'
But after a little thought I could see this is not going to work as it would still be looking for "Perth, Belfast, Bradford, Aberdeen" in one line.

Can anyone suggest how I might do this?

Thanks in advance for any help!!!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-04 : 17:41:26

http://msdn.microsoft.com/en-us/library/ms177682.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-04 : 18:38:32

You could split the string, maybe use IN as Afrika suggested or possibly use LIKE:

DECLARE @Yak TABLE (ID INT, City VARCHAR(20))

INSERT @Yak
SELECT 1, 'Foo'
UNION ALL SELECT 2, 'Belfast'
UNION ALL SELECT 3, 'Bar'
UNION ALL SELECT 4, 'Perth'


DECLARE @CityList VARCHAR(50)
SET @CityList = 'Perth, Belfast, Bradford, Aberdeen'

SELECT *
FROM @Yak
WHERE @CityList LIKE '%' + City + '%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-05 : 00:49:28

quote:

Originally posted by Lamprey

You could split the string, maybe use IN as Afrika suggested or possibly use LIKE:

DECLARE @Yak TABLE (ID INT, City VARCHAR(20))

INSERT @Yak
SELECT 1, 'Foo'
UNION ALL SELECT 2, 'Belfast'
UNION ALL SELECT 3, 'Bar'
UNION ALL SELECT 4, 'Perth'


DECLARE @CityList VARCHAR(50)
SET @CityList = 'Perth, Belfast, Bradford, Aberdeen'

SELECT *
FROM @Yak
WHERE @CityList LIKE '%' + City + '%'

What happens if you Guinea and Papua New guinea among your items
and you search for Guinea? wont it return both? i think it needs a small modification

SELECT *
FROM @Yak
WHERE ',' +@CityList+',' LIKE '%,' + City + ',%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources