CODEWITHSUNDEEP
sqlsql-serverfunctionparsingstored-procedures
Dodgeball
Dodgeball

Reputation: 125

Function to parse a string with different lengths (character/semicolon separated) using T-SQL

I have this 2 strings below

351856040520298,241111;1G,141007024755,A,1437.2453N,12100.2887E,3.10,206,0.8,21010000;
2G,141007024755,10,43.6,14.08,00.18,273295019.6;

and this one

351856040520298,241111;1R,141117003059,A,1420.4629N,12058.7028E,0.0,77,0.9,20000006;2R,
141117003059,11,98.3,12.58,04.10,282098820.9

just wanted to parse it correctly. I used a stored procedure to parse it but cannot parse if the lengths changes. I need some help to develop a function instead but I'm new to function.

I need to parse or split it and insert it to a table using function or stored procedure.

I'm trying to study the function here but cannot understand it without a proper sample like my string for example, Need help. Thanks for all the help

Parse a comma-delimited string

Upvotes: 0

Views: 591

Answers (2)

Deepshikha
Deepshikha

Reputation: 10274

-- Method 1 : split string based only on semi colon and not comma
CREATE FUNCTION [dbo].[ufn_SSVToTable] ( @StringInput VARCHAR(max) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(max) )
AS
BEGIN

    DECLARE @String    VARCHAR(max)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(';', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(';', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END

    RETURN
END

Go

declare @str1 varchar(max)
set @str1  = '351856040520298,241111;1G,141007024755,A,1437.2453N,12100.2887E,3.10,206,0.8,
              21010000;2G,141007024755,10,43.6,14.08,00.18,273295019.6;'

select String as [Method1] from [dbo].[ufn_SSVToTable] ( @str1 ) 

Go

---- Method 2 : split string based on both semi colon and comma

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(max) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(max) )
AS
BEGIN

    DECLARE @String    VARCHAR(max)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END

    RETURN
END

Go

declare @str2 varchar(max)
set @str2  = '351856040520298,241111;1G,141007024755,A,1437.2453N,12100.2887E,3.10,206,0.8,
              21010000;2G,141007024755,10,43.6,14.08,00.18,273295019.6;'

select String as [Method2] from [dbo].[ufn_CSVToTable] ( replace(@str2,';',',')) 

Go

DEMO

Upvotes: 1

Greg the Incredulous
Greg the Incredulous

Reputation: 1846

If you search the web there's plenty of examples of t-sql split functions - here's one I've used before...

http://www.codeproject.com/Tips/666620/T-SQL-Most-Practical-Split-Function

Upvotes: 0

Related Questions