how to send a comma separated list into in clause?

Question

I am trying to pass a list of comma separated email ids a@b.com,s@b.com into sql in clause is my query right please give some suggestions

 SELECT * FROM service.cs_list_of_email_ids where Email in (
        SELECT regexp_substr(:a@b.com,s@b.com,'[^,]+', 1, level) items
        FROM dual
        CONNECT BY regexp_substr(:a@b.com,s@b.com, '[^,]+', 1, level) is not null
    );

http://stackoverflow.com/questions/19885076/how-to-split-string-and-insert-values-into-table-in-sql-server — Ivan Sivak, Mar 31 '15 at 07:18

score 0 · Answer 1 · answered Mar 31 '15 at 07:23

Since you didn't mentioned if you are using Sql Server then you can create this function for splitting the comma separated string

CREATE FUNCTION Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
GO

Then call it as below

SELECT * FROM service.cs_list_of_email_ids where Email in 
       (SELECT val FROM dbo.split(@str,','))

how to send a comma separated list into in clause?

1 Answers1