SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 search records where value is part of string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MikeSaunders
Starting Member

11 Posts

Posted - 09/27/2012 :  08:42:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/27/2012 :  08:46:52  Show Profile  Visit webfred's Homepage  Reply with Quote
Do you want to find "United States of America" with this "unitedstatesofamerica" or not?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

MikeSaunders
Starting Member

11 Posts

Posted - 09/27/2012 :  08:53:51  Show Profile  Reply with Quote
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.

Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/27/2012 :  09:00:44  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

MikeSaunders
Starting Member

11 Posts

Posted - 09/27/2012 :  09:48:38  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000