Split string by character per character occurrence

Question

SSMS 2016 Enterprise

If I have a column with the following string:

$/Harmony/Maintenance/6.0/Dev/

How can I create the following result

Column1 | Column 2 | Column 3 | Column 4 | Column 5 

   $      Harmony   Maintenance   6.0         Dev

the maximum number of / in a string will be 27. So in each row the number of / can vary as well as the string between them.

I need to create 27 column and populate them, if the string only has 5, then the following tables would be null.

I tried solutions using CHARINDEX and SUBSTRING, but I am not being able to make it work as I want so far.

Thank you guys!

Answer 1

I would make a function first to split your string, and then pivot it afterwards.

Function:

CREATE FUNCTION [dbo].[dba_parseString_udf] (
      @stringToParse VARCHAR(8000)  
    , @delimiter     CHAR(1)
)
RETURNS @parsedString TABLE (stringValue VARCHAR(128)) AS
BEGIN

/* Declare variables */
DECLARE @trimmedString  VARCHAR(8000);

/* We need to trim our string input in case the user entered extra spaces */
SET @trimmedString = LTRIM(RTRIM(@stringToParse));

/* Let's create a recursive CTE to break down our string for us */
WITH parseCTE (StartPos, EndPos)
AS
(
    SELECT 1 AS StartPos
        , CHARINDEX(@delimiter, @trimmedString + @delimiter) AS EndPos
    UNION ALL
    SELECT EndPos + 1 AS StartPos
        , CHARINDEX(@delimiter, @trimmedString + @delimiter , EndPos + 1) AS EndPos
    FROM parseCTE
    WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0
)

/* Let's take the results and stick it in a table */  
INSERT INTO @parsedString
SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)
FROM parseCTE
WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0
OPTION (MaxRecursion 8000);

RETURN;   
END

After this i would pivot your results:

----For test purpose-----
DECLARE @Table TABLE (
ID int null,
string nvarchar(500) null
)

INSERT into @Table (ID,String)
VALUES(1,'$/Harmony/Maintenance/6.0/Dev/'),
(2,'$/Harmony/Maintenance/6.0/Dev/Test/')
--------------------------



 select [Column1],[Column2],[Column3],[Column4],[Column5],[Column6],
[Column7],[Column8],[Column9],[Column10],[Column11],[Column12],[Column13]
,[Column14],[Column15],[Column16],[Column17],[Column18],[Column19],
[Column20],[Column21],[Column22],[Column23],[Column24],[Column25],
[Column26],[Column27]
 FROM 
(
 SELECT 'Column' + cast(Row_number() over(Partition BY ID order by ID) as 
varchar(50)) as ColumnRow,
* FROM @Table
CROSS APPLY dbo.[dba_parseString_udf](string,'/')
) AS SOURCETABLE
PIVOT
(
min(stringValue)
FOR ColumnRow IN ([Column1],[Column2],[Column3],[Column4],[Column5],
[Column6],[Column7],[Column8],[Column9],[Column10],[Column11],[Column12],
[Column13]
,[Column14],[Column15],[Column16],[Column17],[Column18],[Column19],
[Column20],[Column21],[Column22],[Column23],[Column24],[Column25],
[Column26],[Column27])
)
AS
PivotTable

Answer 2

Yet another option using a little XML in concert with a CROSS APPLY.

Easy to expand to 27... the pattern is pretty simple

EDIT - Added 2ndToLast - Also added NullIf() which is optional

Example

Select B.*
      ,[2ndToLast] = reverse(Cast('<x>' + replace((Select replace(reverse(A.SomeCol),'/','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).value('/x[3]','varchar(max)'))
 From   @YourTable A
 Cross  Apply (
                Select Pos1 = nullif(xDim.value('/x[1]','varchar(max)'),'')
                      ,Pos2 = nullif(xDim.value('/x[2]','varchar(max)'),'')
                      ,Pos3 = nullif(xDim.value('/x[3]','varchar(max)'),'')
                      ,Pos4 = nullif(xDim.value('/x[4]','varchar(max)'),'')
                      ,Pos5 = nullif(xDim.value('/x[5]','varchar(max)'),'')
                      ,Pos6 = nullif(xDim.value('/x[6]','varchar(max)'),'')
                      ,Pos7 = nullif(xDim.value('/x[7]','varchar(max)'),'')
                      ,Pos8 = nullif(xDim.value('/x[8]','varchar(max)'),'')
                      ,Pos9 = nullif(xDim.value('/x[9]','varchar(max)'),'')
                From  (Select Cast('<x>' + replace((Select replace(A.SomeCol,'/','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

Answer 3

It requiers some coding, but the general idea of mine is:

create dynamicly query, then execute it.

I provided solution, which you can apply. I based it on fixed string (that you provided),, assigned it to @str variable and modified it. But you can put it all in a loop, on every run of the loop you can assign it new string, which will be transformed in a given way. After each loop execution, the insert query will be run.

create table #resultTable(
col1 varchar(50), col2 varchar(50), col3 varchar(50), col4 varchar(50), col5 varchar(50), col6 varchar(50), col7 varchar(50),
col8 varchar(50), col9 varchar(50), col10 varchar(50), col11 varchar(50), col12 varchar(50), col13 varchar(50), col14 varchar(50),
col15 varchar(50), col16 varchar(50), col17 varchar(50), col18 varchar(50), col19 varchar(50), col20 varchar(50), col21 varchar(50),
col22 varchar(50), col23 varchar(50), col24 varchar(50), col25 varchar(50), col26 varchar(50), col27 varchar(50)
)

declare @tableToSplit table(id int identity(1,1), ColumnWithStringToSplit  varchar(2000))
insert into @tableToSplit values ('$/Harmony/Maintenance/6.0/Dev/'),
('$/Harmony/Maintenance/6.0/Dev/123/'),('$/Harmony/Maintenance/6.0/Dev/123/456/')
select * from @tableToSplit

declare @howManyRows int, @id int, @iterator int = 1
select @howManyRows = count(*) from @tableToSplit

declare @i int, @counter int, @values varchar(1000), @query nvarchar(2000)

while @iterator <= @howManyRows
begin
    set @query = 'insert into #resultTable ('
    --get values from table
    select top 1 @id = id, @values = ColumnWithStringToSplit from 
    @tableToSplit
    --count how many strings we will have, which is equal to number of '/' in string
    set @i = len(@values) - len(replace(@values,'/',''))
    --get rid of last forward-slash
    set @values = left(@values, len(@values) - 1)
    --replace all slashes, so it can be used as part of insert statement
    set @values = '(''' + REPLACE(@values, '/', ''',''') + ''')'

    set @counter = 1
    while @counter <= @i
    begin
        set @query = @query + 'col' + cast(@counter as varchar(2)) + ','

        set @counter = @counter + 1
    end
    --get rid of last comma and append rest of the query
    set @query = left(@query, len(@query) - 1) + ') values ' + @values

    exec sp_executesql @query
    --we processed that row, so we need to delete it
    delete from @tableToSplit where id = @id
    set @iterator = @iterator + 1
end

select * from #resultTable

Here's picture of a result (there's 27th column, if you wonder):