locked
Splitting a varchar variable into several varchars separated by comma RRS feed

  • Question

  • User1630393281 posted

    Hello,

    I have a varchar variable in SQL SERVER set like this:

    @Resultat='2001:1, 2001:2, 2018:1, 2018:2'

    I want to have this variable splitted into 4 varchar variables as follows:

    2001:1

    2001:2

    2018:1

    2018:2

    My sql server doesn't support the function STRING_SPLIT and I don't have rights to create a table or a function.

    How can I achieve this ?

    Thanks in advance.

    Monday, July 20, 2020 11:10 AM

All replies

  • User-1330468790 posted

    Hi szejli,

     

    As long as you can connect to a SQL Server instance, you should be able to create temporary tables.

    Here is one solution:

    DECLARE @Resultat NVARCHAR(MAX)='2001:1, 2001:2, 2018:1, 2018:2'
    DECLARE @Position INT=LEN(@Resultat)
    DECLARE @CommaSeperatedValue NVARCHAR(MAX)=''
    
    DROP TABLE IF EXISTS #TempTable
    CREATE TABLE #TempTable(YearMonth nvarchar(50))
    WHILE CHARINDEX(',', @Resultat) > 0
    BEGIN
        SELECT @Position  = CHARINDEX(',', @Resultat)  
        SELECT @CommaSeperatedValue = SUBSTRING(@Resultat, 1, @Position-1)
    
        INSERT INTO #TempTable 
        SELECT LTRIM(RTRIM(@CommaSeperatedValue))
    
        SELECT @Resultat = SUBSTRING(@Resultat, @Position+1, LEN(@Resultat)-@Position)
    
    END
    
    --Add Last Value with TRIM
    IF (LEN(LTRIM(RTRIM(@Resultat)))>0)
    BEGIN
        INSERT INTO #TempTable
        SELECT LTRIM(RTRIM(@Resultat))
    END
    
    SELECT * FROM #TempTable

    Result:

     

    Hope this can help you.

    Best regards,

    Sean

    Monday, July 20, 2020 12:04 PM