CODEWITHSUNDEEP
sql-serverdatetimesplit
Shahbaz Shah
Shahbaz Shah

Reputation: 11

Convert one sql cell data into multiple rows and two columns

I have a SQL table in which I have a nvarchar(MAX) column where multiple barcodes and their datetime values are stored in this format:

2238455|12/10/2019 03:07:55 PM;2238452|12/10/2019 03:13:39 PM;2238454|12/10/2019 03:25:04 PM;2238453|12/10/2019 03:26:44 PM**

A barcode and its datetime are separated from other barcodes using a semicolon ; while a barcode and its datetime are separated using pipe |.

I want to show them in two columns barcode and datetime. Something like this.

Barcode  DateTime
-------------------------------
2238455  12/10/2019 03:07:55 PM
2238452  12/10/2019 03:13:39 PM
2238454  12/10/2019 03:25:04 PM
2238453  12/10/2019 03:26:44 PM

Number of barcodes in one cell are not fixed, it can vary. Is this possible to convert this data into the desired format? I want to write a stored procedure or a view.

Upvotes: 0

Views: 879

Answers (2)

Zhorov
Zhorov

Reputation: 30023

If you use SQL Server 2016+, you may try the following approach using STRING_SPLIT():

Table:

CREATE TABLE Data (
   TextData nvarchar(max)
)
INSERT INTO Data 
   (TextData)
VALUES
   (N'2238455|12/10/2019 03:07:55 PM;2238452|12/10/2019 03:13:39 PM;2238454|12/10/2019 03:25:04 PM;2238453|12/10/2019 03:26:44 PM')

Statement:

SELECT 
   LEFT(s.[value], CHARINDEX(N'|', s.[value]) - 1) AS Barcode,
   RIGHT(s.[value], LEN(s.[value]) - CHARINDEX(N'|', s.[value])) AS [DateTime]
FROM Data d
CROSS APPLY STRING_SPLIT(d.TextData, N';') s

Result:

Barcode DateTime
2238455 12/10/2019 03:07:55 PM
2238452 12/10/2019 03:13:39 PM
2238454 12/10/2019 03:25:04 PM
2238453 12/10/2019 03:26:44 PM

Upvotes: 1

Krishna Varma
Krishna Varma

Reputation: 4260

For older versions, you can use this function to convert the string to table

create Function [dbo].[fn_StringToTable] 
(
    @List Varchar(max)
)
RETURNS @Table TABLE (barcode VARCHAR(100), newdate VARCHAR(100))
AS
BEGIN
    IF RIGHT(@List, 1) <> ';'
    SELECT @List = @List + ';'

    DECLARE @Pos    BIGINT,
            @OldPos BIGINT,
            @rowval varchar(100)
    SELECT  @Pos    = 1,
            @OldPos = 1

    WHILE   @Pos < LEN(@List)
        BEGIN
            SELECT @Pos = CHARINDEX(';', @List, @OldPos)
            select @rowval = LTRIM(RTRIM(SUBSTRING(@List, @OldPos, @Pos - @OldPos)))

            INSERT INTO @Table(barcode, newdate)
            SELECT SUBSTRING(@rowval,1,CHARINDEX('|',@rowval)-1),
                   SUBSTRING(@rowval,CHARINDEX('|',@rowval)+1,LEN(@rowval)) 

            SELECT  @OldPos = @Pos + 1
        END

    RETURN
END

Then call this function with string

select * from  [dbo].[fn_StringToTable] ('2238455|12/10/2019 03:07:55 PM;2238452|12/10/2019 03:13:39 PM;2238454|12/10/2019 03:25:04 PM;2238453|12/10/2019 03:26:44 PM')

OutPut

barcode newdate
2238455 12/10/2019 03:07:55 PM
2238452 12/10/2019 03:13:39 PM
2238454 12/10/2019 03:25:04 PM
2238453 12/10/2019 03:26:44 PM

Upvotes: 0

Related Questions