CODEWITHSUNDEEP
sqlsql-serverstringt-sqlsql-server-2014
Nasir Iqbal
Nasir Iqbal

Reputation: 29

Get Values between Each Comma in Seperate Row in SQL Server

I need to insert multiple rows in a database table from a single string.

Here is my string it will be comma-seperated values

Current string:

batch 1 45665 987655,1228857 76554738,12390 8885858,301297 38998798

What I want is that batch 1 should be ignored or removed and remaining part should be added into the SQL Server database as a separate row for after each comma like this

Table name dbo.MSISDNData

Data
------------------
45665 987655
1228857 76554738
12390 8885858
301297 38998798

and when I query the table it should return the results like this

Query : 

Select data 
from dbo.MSISDNData

Results 

Data
---------------------
45665 987655
1228857 76554738
12390 8885858
301297 38998798

Upvotes: 1

Views: 53

Answers (1)

gotqn
gotqn

Reputation: 43626

Try this:

DECLARE @Data NVARCHAR(MAX) = N'batch 1 45665 987655,1228857 76554738,12390 8885858,301297 38998798'
DECLARE @DataXML XML;

SET @Data = '<a>' + REPLACE(REPLACE(@Data, 'batch 1 ', ''), ',', '</a><a>') + '</a>';
SET @DataXML = @Data;

SELECT LTRIM(RTRIM(T.c.value('.', 'VARCHAR(MAX)'))) AS [Data]
FROM @DataXML.nodes('./a') T(c);

It demonstrates how to split the data. You may need to sanitize it, too - remove the batch 1, perform trimming, etc.

Upvotes: 2

Related Questions