T-Sql: Separate String into Multiple Columns

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

SQL - Splitting string in multiple columns

Here is one way to do it using JSON functions:

select t.name,
    json_value(x.obj, '$[0]') name1,
    json_value(x.obj, '$[1]') name2,
    json_value(x.obj, '$[2]') name2,
    json_value(x.obj, '$[3]') name4
from mytable t
cross apply (values('["' + replace(t.name, '_', '", "') + '"]')) x(obj)

The trick is to manipulate the string to make it look like a JSON array (that' what the cross apply subquery does). Basically this turns a string like 'A_B_C' to '["A", "B", "C"]'. We can then use json_value() to easily access each individual element.

This does not assume anything about the elements being unique. Actually the only requirement is that the string should not contain embedded double quotes.

Demo on DB Fiddle:

name           | name1 | name2 | name2 | name4
:------------- | :---- | :---- | :---- | :----
ABC_DEFG_HIJKL | ABC   | DEFG  | HIJKL | null 
A_B_C          | A     | B     | C     | null 
A_B_C_D        | A     | B     | C     | D    

how to separate string into different columns?

Instead of using split function there is a function called ParseName which returns the specified part of the object which spilts the string delimated by .
Please go through the ParseName link which helped me in writing this query

Declare @Sample Table
(MachineName varchar(max))

Insert into @Sample
values 
('Ab bb zecos'),('a Zeng')

  SELECT 
  Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 1)) As [M1]
 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 2)) As [M2]
 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 3)) As [M3]

  FROM  (Select MachineName from @Sample
  ) As [x] 

T-SQL: Separate String Into Multiple Columns

here is a dynamic sql version. of John's in case you don't know the maximum number of words. Key techniques to accomplish what you want would be split string and pivot (or conditional aggregation). Because you are kind of doing both at once John's method is a nice shortcut.

IF OBJECT_ID('tempdb..#TblName') IS NOT NULL
    BEGIN
        DROP TABLE #TblName
    END

CREATE TABLE #TblName (
    ID INT IDENTITY(1,1)
    ,String VARCHAR(500)
)

INSERT INTO #TblName VALUES ('word1  word2  word3  word4'),('abcd  efgh  ijkl')

DECLARE @NumWords INT

SELECT @NumWords = ISNULL(MAX((LEN(String) - LEN(REPLACE(String,'  ','')))/2 + 1), 0)
FROM
    #TblName

DECLARE @i INT = 1
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
SELECT
    t.Id
    ,t.String
    ,c.*
FROM
    #TblName t 
    CROSS APPLY (
       SELECT

          '
WHILE @i <= @NumWords
BEGIN
    SET @SQL = @SQL
             + IIF(@i > 1,', ','')
             + 'Column' + CAST(@i AS NVARCHAR(MAX)) + '1 = x.value (''/x[' + CAST(@I AS NVARCHAR(MAX)) + ']'',''varchar(max)'')'

    SET @i = @i + 1
END

SET @SQL = @SQL + '
       FROM
          (SELECT CAST(''<x>'' + REPLACE(String,''  '',''</x><x>'') + ''</x>'' as XML) x) a
    ) c'

EXECUTE (@SQL)

Split string into multiple columns TSQL

change query to :

;WITH cte (PK, product,standardcost,currentcost,variance,stages)
AS
(
SELECT 
    [PK],
    [product],
    [standardcost],
    [currentcost],
    [variance],
    CONVERT(XML,'<Product><Attribute>' 
        + REPLACE([stages],',', '</Attribute><Attribute>') 
        + '</Attribute></Product>') AS Prod_Attributes
FROM @tempcostings2021
)
SELECT 
    [PK],
    [product],
    [standardcost],
    [currentcost],
    [variance],
    stages.query('/Product/Attribute[1]').value('/', 'varchar(max)') AS [Stage1],
    stages.query('/Product/Attribute[2]').value('/', 'varchar(max)') AS [Stage2],
    stages.query('/Product/Attribute[3]').value('/', 'varchar(max)') AS [Stage3],
    stages.query('/Product/Attribute[4]').value('/', 'varchar(max)') AS [Stage4]
FROM cte

   

Split multiple values from a string in one column, into multiple columns using SQL Server

With a bit of JSON and assuming you have a known or maximum number of tags

Select A.CompanyName
      ,A.CompanyNumber
      ,Tag1  = JSON_VALUE(S,'$[0]')
      ,Tag2  = JSON_VALUE(S,'$[1]')
      ,Tag3  = JSON_VALUE(S,'$[2]')
From  YourTable A
Cross Apply ( values ( '["'+replace(STRING_ESCAPE(Tags,'json'),';','","')+'"]' ) ) B(S)

Split string into multiple columns

Transforming these strings into jsonb objects is relatively straightforward:

select 
    split_part(id, ':', 1) as id,
    date,
    jsonb_object_agg(split_part(param, '=', 1), split_part(param, '=', 2)) as params
from my_table
cross join unnest(string_to_array(split_part(id, ':', 2), '&')) as param
group by id, date;

Now you can use the solution described in Flatten aggregated key/value pairs from a JSONB field?

Alternatively, if you know the number and names of the parameters, this query is simpler and works well:

select
    id,
    date,
    params->>'type' as type,
    params->>'country' as country,
    params->>'quality' as quality
from (
    select 
        split_part(id, ':', 1) as id,
        date,
        jsonb_object_agg(split_part(param, '=', 1), split_part(param, '=', 2)) as params
    from my_table
    cross join unnest(string_to_array(split_part(id, ':', 2), '&')) as param
    group by id, date
    ) s;

Test it in Db<>fiddle.

In Postgres 14+ you can replace unnest(string_to_array(...)) with string_to_table(...).

SQL server split string into columns by delimiter (dynamic length)

An xml-based solution

declare @tmp table (STRING varchar(500))

insert into @tmp
values
 ('AA.0.HJ')
,('AABBCC.099.0')
,('0.91.JAH21')

;WITH Splitted
AS (
    SELECT STRING
        ,CAST('<x>' + REPLACE(STRING, '.', '</x><x>') + '</x>' AS XML) AS Parts
    FROM @tmp
    )
SELECT STRING
    ,Parts.value(N'/x[1]', 'varchar(50)') AS [First]
    ,Parts.value(N'/x[2]', 'varchar(50)') AS [Second]
    ,Parts.value(N'/x[3]', 'varchar(50)') AS [Third] 
FROM Splitted;

Output:

Split string into two columns

First, Please note that SQL Server 2008 r2 is out of extended support. It's high time to upgrade to a newer version.

For a single string, I would probably use a little dynamic SQL magic trick:

DECLARE @String VARCHAR(MAX) = 'Mak^1,Jak^2,Smith^3,Lee^4,Joseph^5'

DECLARE @Sql VARCHAR(MAX) = 'SELECT Name,Id FROM (VALUES (''' + REPLACE(REPLACE(REPLACE(@String,'''',''''''), ',', '),('''), '^', ''',') + ')) V(Name, Id)';

-- @Sql now contains this:
-- SELECT Name,Id FROM (VALUES ('Mak',1),('Jak',2),('Smith',3),('Lee',4),('Joseph',5)) V(Name, Id)

EXEC(@Sql)

Results:

Name    Id
Mak     1
Jak     2
Smith   3
Lee     4
Joseph  5

---

Related Topics