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]

Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -