search records where value is part of 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

SQL Server 2008 Forums

Transact-SQL (2008)

search records where value is part of string

Author

Topic

MikeSaunders
Starting Member

11 Posts

Posted - 2012-09-27 : 08:42:39

Hello all,

I have a stupid problem i can't solve.
I have a table with countrynames wich look like

countrycode countryalias
----------- ------------
NL nederland
NL netherlands
NL paysbas
US unitedstatesofamerica
US america

Also I have a string e.g. "kevin lives in the United States of America".

Now I want to search for all records in my first table where the field countryalias is part of the string.

Is that possible and if yes how?

Any help is realy appreciated

Best regards Mike

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-27 : 08:46:52

Do you want to find "United States of America" with this "unitedstatesofamerica" or not?

Too old to Rock'n'Roll too young to die.

MikeSaunders
Starting Member

11 Posts

Posted - 2012-09-27 : 08:53:51

Yes, so I strip all the whitespaces out of my string

quote:
Originally posted by webfred

Do you want to find "United States of America" with this "unitedstatesofamerica" or not?

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-27 : 09:00:44

This?

-- sample table
declare @Sample table (countrycode varchar(2), countryalias varchar(255))

-- sample data
insert @Sample
select 'NL', 'nederland' union all
select 'NL', 'netherlands' union all
select 'NL', 'paysbas' union all
select 'US', 'unitedstatesofamerica' union all
select 'US', 'america'

-- show sample data
select * from @Sample

--Also I have a string e.g. "kevin lives in the United States of America".

-- solution
select * from @Sample
where replace('kevin lives in the United States of America',' ','') like '%'+countryalias+'%'

Too old to Rock'n'Roll too young to die.

MikeSaunders
Starting Member

11 Posts

Posted - 2012-09-27 : 09:48:38

Hmm that looks good!!!!
Simpel but effective solution

Thanks a lot

quote:

Originally posted by webfred

This?

-- sample table
declare @Sample table (countrycode varchar(2), countryalias varchar(255))

-- sample data
insert @Sample
select 'NL', 'nederland' union all
select 'NL', 'netherlands' union all
select 'NL', 'paysbas' union all
select 'US', 'unitedstatesofamerica' union all
select 'US', 'america'

-- show sample data
select * from @Sample



--Also I have a string e.g. "kevin lives in the United States of America".

-- solution
select * from @Sample
where replace('kevin lives in the United States of America',' ','') like '%'+countryalias+'%'

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources