finding ints in a varchar filed with numbers

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)

finding ints in a varchar filed with numbers

Author

Topic

ComputerMike
Starting Member

18 Posts

Posted - 2010-05-27 : 13:42:19

Hi,

I have an array of numbers being passed to a procedure as a string, i.e. '8, 10, 17, 19'.

I have a int column in a table. I am trying to determine if the int is in the array (varchar field).

Here the code I have so far...

declare @OtherCode varchar(200)
set @OtherCode = '8, 10, 17, 19'

select @OtherCode

declare @X table
(code int)

insert into @X(code) values(1)
insert into @X(code) values(2)
insert into @X(code) values(3)
insert into @X(code) values(40)
insert into @X(code) values(10)
insert into @X(code) values(20)
insert into @X(code) values(50)
insert into @X(code) values(51)
insert into @X(code) values(60)

select
code,
case when charindex(convert(varchar(3),code), @OtherCode) > 0 then 'is In' else 'is Not In' end as 'IsNumberInMyArray',
@OtherCode as 'MyArray'
from @X

Obviously, 1, as a number, is realy not in the string, but 10 is.

I am thinking this approach will not work, any ideas???

Thanks,

Mike

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 13:52:07

1 You can make use of split function and join it with table
2 or use

declare @OtherCode varchar(200)
set @OtherCode = '8,10,17,19'

select @OtherCode

declare @X table
(code int)

insert into @X(code) values(1)
insert into @X(code) values(2)
insert into @X(code) values(3)
insert into @X(code) values(40)
insert into @X(code) values(10)
insert into @X(code) values(20)
insert into @X(code) values(50)
insert into @X(code) values(51)
insert into @X(code) values(60)

select
code,
case when charindex(','+convert(varchar(3),code)+',', ','+@OtherCode+',') > 0 then 'is In' else 'is Not In' end as 'IsNumberInMyArray',
@OtherCode as 'MyArray'
from @X

Madhivanan

Failing to plan is Planning to fail

ComputerMike
Starting Member

18 Posts

Posted - 2010-05-27 : 14:00:31

Thanks,

I added...

select @OtherCode = replace(@OtherCode, ' ','')

in case user adds extra spaces

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources