Converting String List into Int List in SQL

Converting String List into Int List in SQL

It is possible to send an int list to your stored procedure using XML parameters. This way you don't have to tackle this problem anymore and it is a better and more clean solution.

have a look at this question:
Passing an array of parameters to a stored procedure

or check this code project:
http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty

However if you insist on doing it your way you could use this function:

CREATE FUNCTION [dbo].[fnStringList2Table]
(
    @List varchar(MAX)
)
RETURNS 
@ParsedList table
(
    item int
)
AS
BEGIN
    DECLARE @item varchar(800), @Pos int

    SET @List = LTRIM(RTRIM(@List))+ ','
    SET @Pos = CHARINDEX(',', @List, 1)

    WHILE @Pos > 0
    BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @item <> ''
        BEGIN
            INSERT INTO @ParsedList (item) 
            VALUES (CAST(@item AS int))
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)
    END

    RETURN
END

Call it like this:

SELECT      *
FROM        Table
WHERE status IN (SELECT * from fnStringList2Table(@statuslist))

Convert varchar list to int in Sql Server

Well on production server I'd write some table valued function for splitting lists, but if you need quick ad-hoc query, this xml trick could work

declare @listOfPageIds varchar(50), @data xml
declare @temp table(id int)

select @listofPageIds = '2, 3, 4, 5, 6, 7, 14, 15';
select @data = '<t>' + replace(@listofPageIds, ', ', '</t><t>') + '</t>'

insert into @temp
select
    t.c.value('.', 'int') as id
from @data.nodes('t') as t(c)

select * from @temp

sql fiddle demo

Convert string to a list of strings in SQL Server

Simplest version.

SQL

DECLARE @input VARCHAR(100) = 'COL1  , COL2   , COL3'
    , @separator CHAR(2) = ', '
    , @quote CHAR(1) = CHAR(39)
    , @output VARCHAR(500);

-- Method #1
SET @output = @quote + REPLACE(@input,  @separator, CONCAT(@quote,',', @quote)) + @quote;

SELECT  @output;

/*
Separator could be ',' or ', ' (with space after the coma), ' , ' (or space before and after)
*/
-- Method #2, SQL Server 2017 onwards
SELECT @output = STRING_AGG(CONCAT(@quote, TRIM(value), @quote), ',')
FROM STRING_SPLIT(@input, ',');

SELECT @output;

Convert int list to string with comma SQL Server

using the stuff() with select ... for xml path ('') method of string concatenation.

create table t (ProductId int);
insert into t values (68) ,(74) ,(58) ,(64) ,(67);

select 
    ProductIds = stuff((
        select ','+convert(varchar(10),ProductId)
        from t
        for xml path (''), type).value('.','nvarchar(max)')
      ,1,1,'')

rextester demo: http://rextester.com/RZQF31435

returns:

+----------------+
|   ProductIds   |
+----------------+
| 68,74,58,64,67 |
+----------------+

edit: In SQL Server 2017+, you can use string_agg(), and the performance appears to be the same based on Jeffry Schwartz's article: Should I Replace My FOR XML PATH String Merges with String_agg?

How to convert list of IDs formatted in varchar into int

You have to conver the the comma seperated value to table. Do this for SQL SERVER

CREATE FUNCTION SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END

SELECT * FROM MyTable where MyTableId in 
      (SELECT * FROM dbo.SplitString('26,27,28', ','))  --(26,27,28)

How to convert List string to List int ?

listofIDs.Select(int.Parse).ToList()

---

Related Topics