CODEWITHSUNDEEP
sql-server-2008
Vijay Singh Rana
Vijay Singh Rana

Reputation: 1090

passing comma deligma string to IN clause in sql server

SQL statement:

(select top 1 [egrp_name] from [Enotify Group] where [egrp_id] in (a.grp_id) )

e value of a.grp_id is '0,1145' and i am getting error

Conversion failed when converting the varchar value '0,1145' to data type int.

Can anybody tell me how can i change '0,1145' to 0,1145 in above case, so my query does work and also if their is any other way to do this

Upvotes: 0

Views: 103

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use a split-string function to change your comma delimited string into a table.

select top(1) [egrp_name] 
from [Enotify Group] 
where [egrp_id] in (
                   select Value 
                   from dbo.SplitInts(a.grp_id)
                   );

One version of a split-string function that you can use if you like:

create function dbo.SplitInts(@Values nvarchar(max)) returns table with schemabinding
as
return 
(  
  select T2.X.value(N'.', N'int') as Value
  from (select cast(N'<?X '+replace(@Values, N',', N'?><?X ') + N'?>' as xml).query(N'.')) as T1(X)
    cross apply T1.X.nodes(N'/processing-instruction("X")') as T2(X)
);

Or you can use like.

select top(1) [egrp_name] 
from [Enotify Group] 
where ','+a.grp_id +',' like '%,'+cast(egrp_id as varchar(11))+',%' ;

Upvotes: 1

Related Questions