CODEWITHSUNDEEP
sqlsql-server
Phil
Phil

Reputation: 11

SQL Server split string values into rows and columns and insert into table

I have a string/text element that I need to split and insert into a table.

I have the text values of

123456|House,123567|Flat,789463|Car,635491|Lorry,

Where the number value (nvalue) needs to go in the first column, and the type eg. House needs to be in the second.

The values before the "," need to be on the same row. I would be looking for a table resembling 

╔══════════╦═══════════╗
║ nvalue   ║ Type      ║
╠══════════╬═══════════╣
║   123456 ║     House ║
║   123567 ║      Flat ║
║   789463 ║       Car ║
║   635491 ║     Lorry ║
╚══════════╩═══════════╝

I'm trying to use the SQL code

INSERT INTO TABLE resultsTable
SELECT 
nvalue({status}, ';')[255],
type({status}| ';')[255],

but i'm not having any luck.

{status} is a text field where the string is located.

Upvotes: 0

Views: 1266

Answers (3)

user7715598
user7715598

Reputation:

Using SUBSTRING(), Xml and Cross apply we can get desired result

DECLARE @Value NVARCHAR(max) = ',123456|House,123567|Flat,789463|Car,635491|Lorry,'
DECLARE @DYVALUE TABLE (VALUE NVARCHAR(MAX))

INSERT INTO @DYVALUE (VALUE)
SELECT @VALUE

;WITH cte
AS (
    SELECT Split.a.value('.', 'VARCHAR(1000)') AS Value
    FROM (
        SELECT CAST('<S>' + REPLACE(Value, ',', '</S><S>') + '</S>' AS XML) AS Value
        FROM @DyValue
        ) AS A
    CROSS APPLY Value.nodes('/S') AS Split(a)
    )
SELECT *
FROM (
    SELECT SUBSTRING(Value, 0, CHARINDEX('|', Value)) AS nvalue
        ,SUBSTRING(Value, CHARINDEX('|', Value) + 1, LEN(Value)) AS [Type]
    FROM Cte
    ) DT
WHERE nvalue <> ''
    AND [Type] <> ''

Result:

+--------+-------+
| nvalue | Type  |
+--------+-------+
| 123456 | House |
| 123567 | Flat  |
| 789463 | Car   |
| 635491 | Lorry |
+--------+-------+

Upvotes: 0

SqlZim
SqlZim

Reputation: 38043

In SQL Server 2016+ you can use string_split().

In SQL Server pre-2016, using a CSV Splitter table valued function by Jeff Moden along with left() and stuff() (or right()) with charindex():

declare @status nvarchar(max) = '123456|House,123567|Flat,789463|Car,635491|Lorry,'

select 
    nvalue = left(s.Item,charindex('|',s.Item)-1)
  , [Type] = stuff(s.Item,1,charindex('|',s.Item),'')
from dbo.DelimitedSplitN4K(@status,',') s
where s.Item <>''

rextester demo: http://rextester.com/QQZUC78477

returns: 

+--------+-------+
| nvalue | Type  |
+--------+-------+
| 123456 | House |
| 123567 | Flat  |
| 789463 | Car   |
| 635491 | Lorry |
+--------+-------+

splitting strings reference:

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270421

You can either use split() from the web or in SQL Server 2016. Then:

with lines(line) as (
      select l.*
      from dbo.split(@str, '|') l
     )
insert into resultsTable (nvalue, type)
    select left(line, charindex(',', line) - 1),
           stuff(line, charindex(',', line), len(line), '')
    from lines;

Upvotes: 0

Related Questions