locked
Extract string between delimiters RRS feed

  • Question

  • Hi All,

    HI

    I want to extract string between the delimters   where data is as follows:

     ,4,34,56    output required

    4
    34
    56

    how to extract this substring from the main string.


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

    Wednesday, February 13, 2013 5:03 AM

Answers

  • DECLARE @Sample TABLE
    (
    ID VARCHAR(100),
    Data VARCHAR(100)
    )

    INSERT @Sample
    VALUES ('BAU', '4,3,2,1')

    DECLARE @ID VARCHAR(100) 
    SET @ID= 'BAU'
    -- Solution here
    ;WITH cteSource(ID, Data)
    AS (
    SELECT ID,
    CAST('<v><i>' + REPLACE(REPLACE(Data, ',', '</i></v><v><i>'), '..', '</i><i>') + '</i></v>' AS XML) AS Data
    FROM @Sample
    WHERE ID = @ID
    )
    SELECT s.ID,
    v.value('i[1]', 'VARCHAR(100)') AS val

    FROM cteSource AS s
    CROSS APPLY Data.nodes('v') AS n(v)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, February 13, 2013 6:56 AM
    Answerer

All replies

  • Hi Sufian,

    Use this code below,

    DECLARE @text VARCHAR (50)
    DECLARE @Delimiter CHAR (1)
    DECLARE @length INT
    DECLARE @table TABLE
      (
         value VARCHAR(50)
      )
    
    SET @text = '1,2,4,887,77889,1,132,4,5,455,4478'
    SET @Delimiter = ','
    SET @length = Len(@text)
    
    WHILE ( @length != 0 )
      BEGIN
          IF( Charindex(@delimiter, @text) <> 0 )
            BEGIN
                INSERT INTO @table
                            (value)
                SELECT LEFT(@text, Charindex(@Delimiter, @text) - 1)
    
                SET @text = RIGHT(@text, Len (@text) - Charindex(@Delimiter, @text))
                SET @length= Len(@text)
            END
          ELSE
            BEGIN
                INSERT INTO @table
                            (value)
                SELECT @text
    
                SET @length = 0
            END
      END
    
    SELECT *
    FROM   @table 
    

    Thanks

    Wednesday, February 13, 2013 5:13 AM
  • Try this

    Declare @Data Varchar(500)=' ,4,34,56',
    @Xml  Xml, 
    @S  Nvarchar(Max)

    Set  @Xml = N'<root><r>' + replace(@Data,',','</r><r>') + '</r></root>'
    Select * 
      From
    (
    Select Val.value('.','varchar(150)') As Data
     From @Xml.nodes('//root/r') As A(Val)
    ) Fin
    Where Data <> ''


    Please have look on the comment

    Wednesday, February 13, 2013 5:13 AM
  • You can use a split functions

    have a look at this link

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648


    Satheesh

    Wednesday, February 13, 2013 6:17 AM
  • Thanks all for the response

    based on some query i am extrasting some data in below format

    Name Value
    BAU ,4,45,3,

    Looking to format as below

    Name Value
    BAU 4
    BAU 45
    BAU 3


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

    Wednesday, February 13, 2013 6:48 AM
  • Hi,
    Try this
    CREATE FUNCTION dbo.Split
    (
    	@RowData nvarchar(2000),
    	@SplitOn nvarchar(5)
    )  
    RETURNS @RtnValue table 
    (
    	Id int identity(1,1),
    	Data nvarchar(100)
    ) 
    AS  
    BEGIN 
    	Declare @Cnt int
    	Set @Cnt = 1
    
    	While (Charindex(@SplitOn,@RowData)>0)
    	Begin
    		Insert Into @RtnValue (data)
    		Select 
    			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
    
    		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    		Set @Cnt = @Cnt + 1
    	End
    	
    	Insert Into @RtnValue (data)
    	Select Data = ltrim(rtrim(@RowData))
    
    	Return
    END
    
    
    GO
    
    declare @tab table(Name varchar(10),Data Varchar(500))
    insert into @tab values('BAU', ',4,45,3');
    
    
    
    select * from @tab T cross apply dbo.split(t.Data,',') s
    where s.data <>''


    Satheesh



    Wednesday, February 13, 2013 6:56 AM
  • DECLARE @Sample TABLE
    (
    ID VARCHAR(100),
    Data VARCHAR(100)
    )

    INSERT @Sample
    VALUES ('BAU', '4,3,2,1')

    DECLARE @ID VARCHAR(100) 
    SET @ID= 'BAU'
    -- Solution here
    ;WITH cteSource(ID, Data)
    AS (
    SELECT ID,
    CAST('<v><i>' + REPLACE(REPLACE(Data, ',', '</i></v><v><i>'), '..', '</i><i>') + '</i></v>' AS XML) AS Data
    FROM @Sample
    WHERE ID = @ID
    )
    SELECT s.ID,
    v.value('i[1]', 'VARCHAR(100)') AS val

    FROM cteSource AS s
    CROSS APPLY Data.nodes('v') AS n(v)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, February 13, 2013 6:56 AM
    Answerer