compare a substring - 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 2005 Forums

Transact-SQL (2005)

compare a substring

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-09 : 15:17:52

Hi

I have 2 columns in a table that holds information like this...

column 1 column2
1234 1010_1234_59somestring.pdf
1234 1010_4566_67somestring.pdf
1222 1010_1222_59somestring.pdf

Is it somhow possible to compare the column 1 value with the "1234" part in column 2? So that I would only retrieve the rows that have the same value here .. xxx_valuetocompare_xxx as in column 1

regards

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-09 : 15:24:52

where column1 = substring(column2, 6, 4)

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-09 : 15:26:07

Im assuming Column1 is int and Column2 is varchar.

select * from YourTable
where Column2 like '%'+cast(Column1 as varchar)+'%'

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-01-09 : 15:35:05

That worked excellent, Thanks a lot!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-10 : 01:21:37

and more reliabily

select * from YourTable
where Column2 like '%[_]'+cast(Column1 as varchar)+'[_]%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources