string comparison - 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

SQL Server 2008 Forums

Transact-SQL (2008)

string comparison

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-12-01 : 16:59:28

I would need to compare two strings, like:
'GEL, ORMD88-76/P' vs. 'GEL ORMD88 76 P'
Some of the strings contain commas and other special characters. I would like to query for records where string1 <> string2, regardless of the special characters.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-01 : 17:14:28

Don't think it is the best way...but one way is to replace all the special characters with '' or space and then do the compare.

declare @t varchar(100)
declare @s varchar(100)
set @t = 'GEL, ORMD88-76/P' 
set @s = 'GEL ORMD88 76 P'

select case when replace(replace(replace(@t,',',''),'-',' '),'/',' ') = @s then 'match' else 'different' end

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-01 : 18:48:45

You may also want to check into SOUNDEX and DIFFERENCE. The only sure-fire way, though, is to write a function that removes all potential special characters.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 01:16:21

quote:
Originally posted by jimf

You may also want to check into SOUNDEX and DIFFERENCE. The only sure-fire way, though, is to write a function that removes all potential special characters.

Jim

Everyday I learn something that somebody else already knew

I think using SOUNDEX etc will result in more broader search matches. From first post, I think OP is looking for matches irrespective of some intermediate special characters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-02 : 04:49:58

You can also clean all unwanted characters
http://beyondrelational.com/blogs/madhivanan/archive/2009/05/11/removing-unwanted-characters.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources