locked
Grab All Values As Separate Columns Between Special Character in a String RRS feed

  • Question

  • Hi Friends,

    I am trying to separate data in a string into different column. Below is the sample data and I need it to be:

    DECLARE @String VARCHAR(MAX) = '/Australia/New South Wales/Richmond'

    Output:

    COl1         Col2                                   Col3

    Australia   New South Wales                Richmond

    Please guide me, I am able to grab the position of '/'

    with cte as
    (select '/Australia/New South Wales/Richmond' as name
    ), 
    pos as
    (select patindex('%/%',name) pos, name from cte
    union all
    select pos+patindex('%/%',substring(name, pos+1, len(name))) pos, name from pos
    where patindex('%/%',substring(name, pos+1, len(name)))>0
    )
    select pos from pos    

    Output

    1
    11
    27

    Want to do some magic where it should automatically give me output in those 3 columns rather than just position.

    Regards

     


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Thursday, March 1, 2018 12:34 AM

Answers

  • Create a function:

    CREATE FUNCTION [dbo].[fn_ParseStringByDelimiter] (
    	@inputString varchar(max),
    	@delimiter char(1)
    )
    RETURNS @ReturnTable TABLE (
    	RowID int IDENTITY(1, 1),
    	Item varchar(128)
    )
    AS
    BEGIN
    	DECLARE @index int = 1;
    	DECLARE @item varchar(128) = '';
    
    	SET @inputString = REPLACE(@inputString, ' ', '');
    
    	IF LEN(@inputString) = 0 OR @inputString IS NULL
    	BEGIN
    		RETURN;
    	END
    	ELSE
    	BEGIN
    		SET @inputString = 
    			CASE 
    				WHEN RIGHT(@inputString, 1) = @delimiter
    					THEN SUBSTRING(@inputString, 1, LEN(@inputString) - 1)
    				ELSE
    					@inputString
    			END
    	END
    
    	WHILE @index <> 0
    	BEGIN
    		SET @index = CHARINDEX(@delimiter, @inputString);
    
    		IF @index <> 0
    		BEGIN
    			SET @item = LEFT(@inputString, @index - 1);
    		END
    		ELSE
    		BEGIN
    			SET @item = @inputString;
    		END
    
    		IF LEN(@item) > 0
    		BEGIN
    			INSERT INTO @ReturnTable(Item) VALUES(@item);
    		END
    
    		SET @inputString = RIGHT(@inputString, LEN(@inputString) - @index);
    
    		IF LEN(@inputString) = 0
    		BEGIN
    			RETURN;
    		END
    	END
    
    	RETURN;
    END
    GO

    and try this:

    DECLARE @String VARCHAR(MAX) = '/Australia/New South Wales/Richmond';
    DECLARE @Temp TABLE (
    	Id int,
    	Item varchar(100)
    );
    INSERT INTO @Temp(Id, Item)
    SELECT * FROM [dbo].[fn_ParseStringByDelimiter](@String, '/');
    
    SELECT 
    Col1 = (SELECT Item FROM @Temp WHERE Id = 1),
    Col2 = (SELECT Item FROM @Temp WHERE Id = 2),
    Col3 = (SELECT Item FROM @Temp WHERE Id = 3);


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by GURSETHI Thursday, March 1, 2018 2:25 AM
    Thursday, March 1, 2018 2:15 AM

All replies

  • For SQL 2016, try this

    DECLARE @String VARCHAR(MAX) = '/Australia/New South Wales/Richmond';
    
    DECLARE @Temp TABLE (
    	Id int IDENTITY(1, 1),
    	Item varchar(100)
    );
    INSERT INTO @Temp(Item)
    SELECT value FROM STRING_SPLIT(@String, '/') WHERE value <> '';
    
    SELECT 
    Col1 = (SELECT Item FROM @Temp WHERE Id = 1),
    Col2 = (SELECT Item FROM @Temp WHERE Id = 2),
    Col3 = (SELECT Item FROM @Temp WHERE Id = 3);


    A Fan of SSIS, SSRS and SSAS

    Thursday, March 1, 2018 1:37 AM
  • Sorry dear its SQL 2012 :)

    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Thursday, March 1, 2018 1:40 AM
  • Create a function:

    CREATE FUNCTION [dbo].[fn_ParseStringByDelimiter] (
    	@inputString varchar(max),
    	@delimiter char(1)
    )
    RETURNS @ReturnTable TABLE (
    	RowID int IDENTITY(1, 1),
    	Item varchar(128)
    )
    AS
    BEGIN
    	DECLARE @index int = 1;
    	DECLARE @item varchar(128) = '';
    
    	SET @inputString = REPLACE(@inputString, ' ', '');
    
    	IF LEN(@inputString) = 0 OR @inputString IS NULL
    	BEGIN
    		RETURN;
    	END
    	ELSE
    	BEGIN
    		SET @inputString = 
    			CASE 
    				WHEN RIGHT(@inputString, 1) = @delimiter
    					THEN SUBSTRING(@inputString, 1, LEN(@inputString) - 1)
    				ELSE
    					@inputString
    			END
    	END
    
    	WHILE @index <> 0
    	BEGIN
    		SET @index = CHARINDEX(@delimiter, @inputString);
    
    		IF @index <> 0
    		BEGIN
    			SET @item = LEFT(@inputString, @index - 1);
    		END
    		ELSE
    		BEGIN
    			SET @item = @inputString;
    		END
    
    		IF LEN(@item) > 0
    		BEGIN
    			INSERT INTO @ReturnTable(Item) VALUES(@item);
    		END
    
    		SET @inputString = RIGHT(@inputString, LEN(@inputString) - @index);
    
    		IF LEN(@inputString) = 0
    		BEGIN
    			RETURN;
    		END
    	END
    
    	RETURN;
    END
    GO

    and try this:

    DECLARE @String VARCHAR(MAX) = '/Australia/New South Wales/Richmond';
    DECLARE @Temp TABLE (
    	Id int,
    	Item varchar(100)
    );
    INSERT INTO @Temp(Id, Item)
    SELECT * FROM [dbo].[fn_ParseStringByDelimiter](@String, '/');
    
    SELECT 
    Col1 = (SELECT Item FROM @Temp WHERE Id = 1),
    Col2 = (SELECT Item FROM @Temp WHERE Id = 2),
    Col3 = (SELECT Item FROM @Temp WHERE Id = 3);


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by GURSETHI Thursday, March 1, 2018 2:25 AM
    Thursday, March 1, 2018 2:15 AM
  • Thanks Guoxiong,

    This work like a charm...


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Thursday, March 1, 2018 2:25 AM