T-SQL How to Convert Comma Separated String of Numbers to Integer

t-sql Convert comma separated string into int, without using user created function

You don't want to cast that list into an int, but into a list of ints.

There is no cast operator or function for that, but you can use dynamic SQL to get around it.

Basically you write

EXECUTE('SELECT * FROM tbl_Employee WHERE employeeID IN ('+@intArray+')')

Be aware of SQL injection attacks though!

T-SQL How to convert comma separated string of numbers to integer

If LegalIssue contains a string of comma-delimited numbers, then you really want an association table. Lacking that, here is a convenient (but not efficient) way to do the join:

SELECT F.[FDTitle], E.PrimaryOpID as [FD Primary OP ID], F.County as [FD County],
       F.Status as [FD Status], F.IssueDate as [FD Date]
FROM [dbo].[tbl_FinalDetMain] F LEFT OUTER JOIN
      [dbo].[tbl_lk_Exemptions_FD] E
      ON ','+F.LegalIssue+',' like '%,'cast(E.ID as varchar(255))+',%'
WHERE F.[FDNbr] = '2013-0041';

This prepends and postpends the list with commas to avoid conflicts, such as finding "10" in "1,100,1000".

How to convert NVARCHAR comma-delimited list to INT?

You don't want to convert it to an int. Instead, use like for the comparison:

select *
from table
where ', '+@list+', ' like ', '+cast(id as varchar(255)) + ', ';

This has the downside that the query will not use indexes. If that is important, then you can use dynamic SQL:

DECLARE @list nvarchar(10) = '1, 2, 3, 4, 5, 6';
declare @sql nvarchar(max) = 'SELECT * FROM table WHERE ID IN ('+ @list +')';

exec sp_executesql @sql;

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

How do I convert a number to a numeric, comma-separated formatted string?

For SQL Server 2012, or later, an easier solution is to use FORMAT ()^{Documentation}.

EG:

SELECT Format(1234567.8, '##,##0') 

Results in: 1,234,568

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

---

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

---

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

---

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

---

Related Topics