Hi, I want to convert the comma separated nvarchar to int list in sql

Question

I want to convert the comma separated nvarchar to int list in sql.How I can achieve that?

Answer 1

I created a function as

    USE [QA]
    GO
    /****** Object:  UserDefinedFunction [dbo].[NvarcharToIntList]    Script Date: 5/29/2017 2:42:50 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER FUNCTION [dbo].[NvarcharToIntList] (@InStr VARCHAR(MAX))
    RETURNS @TempTab TABLE
       (id int not null)
    AS
    BEGIN
        ;-- Ensure input ends with comma
        SET @InStr = REPLACE(@InStr + ',', ',,', ',')
        DECLARE @SP INT
    DECLARE @VALUE VARCHAR(1000)
    WHILE PATINDEX('%,%', @INSTR ) <> 0 
    BEGIN
       SELECT  @SP = PATINDEX('%,%',@INSTR)
       SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
       SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
       INSERT INTO @TempTab(id) VALUES (@VALUE)
    END
        RETURN
    END
@users = '158,159'

then I am using it as dbo.ResidentAssessment.LastUpdatedBy in(SELECT * FROM dbo.NvarcharToIntList(@users))

Answer 2

Starting from SQL 2016 there is a STRING_SPLIT function

from the doc

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  

SELECT value  
FROM STRING_SPLIT(@tags, ',');