Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to compare two string variables Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2008 5:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 21, 2010 10:59 PM
Points: 21, Visits: 55
Hi,

I need to find out if the first string is in second string. How can I compare two string variables as following:

declare @s1 as varchar
declare @s2 as varchar

set @s1 = '1232'
set @s2 = 'tete,1232'

select patindex('%1232%','tete,1232') will return a number

Can I do something like select patindex (@s1, @s2)?
Post #457722
Posted Tuesday, February 19, 2008 8:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
Yes you can... like this...

DECLARE @S1 VARCHAR(100)
DECLARE @S2 VARCHAR(100)

SET @S1 = '5'
SET @S2 = '1,2,3,4,55,6'

SELECT PATINDEX('%'+@S1+'%',@S2)

But, you notice that in this case, the answer is incorrect. There is no "5" in @S2... the number is incorrectly being returned because of the "55" the "5" picks up on.

If you're not going to split the data, then you must wrap the delimiters in the search on both @S1 and @S2... like this...

DECLARE @S1 VARCHAR(100)
DECLARE @S2 VARCHAR(100)

SET @S1 = '5'
SET @S2 = '1,2,3,4,55,6'

SELECT PATINDEX('%,'+@S1+',%' , ','+@S2+',')



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #457787
Posted Wednesday, February 20, 2008 1:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 1:56 AM
Points: 438, Visits: 754
try this one also ....

DECLARE @s1 varchar(16)
DECLARE @s2 varchar(16)

SET @s1 = '1232'
SET @s2 = 'tete,1232'

SELECT @s1,@s2

SELECT SUBSTRING(@s2,CHARINDEX(@s1,@s2,1),LEN(@s1))

---
Post #457867
Posted Wednesday, February 20, 2008 10:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 21, 2010 10:59 PM
Points: 21, Visits: 55
Thanks !
Post #458131
Posted Wednesday, February 20, 2008 11:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
sqluser (2/20/2008)
try this one also ....

DECLARE @s1 varchar(16)
DECLARE @s2 varchar(16)

SET @s1 = '1232'
SET @s2 = 'tete,1232'

SELECT @s1,@s2

SELECT SUBSTRING(@s2,CHARINDEX(@s1,@s2,1),LEN(@s1))

---

Careful!!!! You must still include the delimiter wrapping or you will get false returns.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #458132
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse