Compare int column with varchar column

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)

Compare int column with varchar column

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-09-18 : 03:03:55

I have a table with an ID column of the type int and another column containing a FileName and Path of the type varchar.
I would like to find all records of that table where the value of the varchar column contains the value of the int column.
Here is an example and one of the queries I've tried. Obviously this doesn't work. How can I achieve this? Thanks.

-- Test data
declare @testtable as table(ID int, DocumentName varchar(100))

insert into @testtable
(ID, DocumentName)
values
(101,'a path\a.doc'),
(102,'other path\321.doc'),
(103,'foo\1.xls'),
(104,'c:\bla104.pdf'),
(105,'321\105.doc'),
(106,'folder\1.doc')

-- Query
select *
from @testtable
where DocumentName like
(
  select '%' + convert(varchar,ID) + '%' from @testtable
)

-- This query gives an error "Subquery returned more than 1 value...." but shows what I'm trying to do
-- I would like a query that returns the rows with ID 104, 105

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-18 : 03:06:06

[code]select *
from @testtable
where DocumentName like '%' + convert(varchar(10), ID) + '%'[/code]

KH
[spoiler]Time is always against us[/spoiler]

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-09-18 : 03:16:12

of course... was making it harder than has to be.
Thanks khtan!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources