none
How to Find Specific value from a string RRS feed

  • Question

  • Hello Everyone,

    have a small question on SQL:

    I have a String this way

    Declare @Dim1 Varchar(500)
    Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'
    out of the above string, I just need to bring below OUTPUT:  please help with  your inputs. Thanks in Advance

    WIRECENTER,Channel_Name,Speed, Tenure


    Thanks, Please Help People When they need..!!! Mark as answered if your problem is solved.


    • Edited by BKomm Tuesday, May 9, 2017 4:53 PM
    Tuesday, May 9, 2017 4:34 PM

Answers

  • Declare @Dim1 Varchar(500)
    Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'
    declare @str varchar(100)
    set @str = ''
    
    while(charindex(',',@Dim1,1) > 0)
    begin
    if len(@str) > 0
    	set @str = @str + ', '
    set @str = @str + substring(@Dim1,charindex('.',@Dim1,1)+1,charindex(',',@Dim1,1) - charindex('.',@Dim1,1)-1)
    set @Dim1 = substring(@Dim1,charindex(',',@Dim1,1)+1, len(@dim1))
    end
    
    select @str

    • Edited by SQLNeophyte Tuesday, May 9, 2017 5:09 PM
    • Marked as answer by BKomm Tuesday, May 9, 2017 5:30 PM
    Tuesday, May 9, 2017 5:08 PM
  • Declare @Dim1 Varchar(500)
    Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'
    
    DECLARE @myXML AS XML = N'<H><r>' + REPLACE(@Dim1, ',', '</r><r>') + '</r></H>'
     
    
     ;with mycte as (
    Select stuff(val,1,charindex('.',val),'')  val,rn FROM (
    SELECT Vals.id.value('.', 'NVARCHAR(50)') AS val
    ,row_number() Over(Order By getdate()) as rn
    FROM @myXML.nodes('/H/r') AS Vals(id) ) t
    )
    
    
     
    SELECT distinct  Stuff(( SELECT ',' + t2.val
               FROM mycte t2
             
              
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
      FROM mycte t1
    ;
     

    • Marked as answer by BKomm Tuesday, May 9, 2017 5:31 PM
    Tuesday, May 9, 2017 5:10 PM
    Moderator
  • declare @Val1 varchar(50),
    @Val2 varchar(50),
    @Val3 varchar(50),
    @Val4 varchar(50)
    
    Declare @Dim1 Varchar(500)
    Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'
    
    DECLARE @myXML AS XML = N'<H><r>' + REPLACE(@Dim1, ',', '</r><r>') + '</r></H>'
     
    
     ;with mycte as (
    Select stuff(val,1,charindex('.',val),'')  val,rn FROM (
    SELECT Vals.id.value('.', 'NVARCHAR(50)') AS val
    ,row_number() Over(Order By getdate()) as rn
    FROM @myXML.nodes('/H/r') AS Vals(id) ) t
    )
    
    select @Val1=max(Case when rn=1 then val end) ,
     @Val2=max(Case when rn=2 then val end),
     @Val3=max(Case when rn=3 then val end),
     @Val4=max(Case when rn=4 then val end) 
    from mycte
    print @Val1
    print @Val2
    print @Val3
    print @Val4

    • Marked as answer by BKomm Wednesday, May 10, 2017 3:47 AM
    Tuesday, May 9, 2017 10:52 PM
    Moderator

All replies

  • Declare @Dim1 Varchar(500)
    Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'
    declare @str varchar(100)
    set @str = ''
    
    while(charindex(',',@Dim1,1) > 0)
    begin
    if len(@str) > 0
    	set @str = @str + ', '
    set @str = @str + substring(@Dim1,charindex('.',@Dim1,1)+1,charindex(',',@Dim1,1) - charindex('.',@Dim1,1)-1)
    set @Dim1 = substring(@Dim1,charindex(',',@Dim1,1)+1, len(@dim1))
    end
    
    select @str

    • Edited by SQLNeophyte Tuesday, May 9, 2017 5:09 PM
    • Marked as answer by BKomm Tuesday, May 9, 2017 5:30 PM
    Tuesday, May 9, 2017 5:08 PM
  • Declare @Dim1 Varchar(500)
    Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'
    
    DECLARE @myXML AS XML = N'<H><r>' + REPLACE(@Dim1, ',', '</r><r>') + '</r></H>'
     
    
     ;with mycte as (
    Select stuff(val,1,charindex('.',val),'')  val,rn FROM (
    SELECT Vals.id.value('.', 'NVARCHAR(50)') AS val
    ,row_number() Over(Order By getdate()) as rn
    FROM @myXML.nodes('/H/r') AS Vals(id) ) t
    )
    
    
     
    SELECT distinct  Stuff(( SELECT ',' + t2.val
               FROM mycte t2
             
              
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
      FROM mycte t1
    ;
     

    • Marked as answer by BKomm Tuesday, May 9, 2017 5:31 PM
    Tuesday, May 9, 2017 5:10 PM
    Moderator
  • Declare @Dim1 Varchar(500)
    Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'
    declare @str varchar(100)
    set @str = ''
    
    while(charindex(',',@Dim1,1) > 0)
    begin
    if len(@str) > 0
    	set @str = @str + ', '
    set @str = @str + substring(@Dim1,charindex('.',@Dim1,1)+1,charindex(',',@Dim1,1) - charindex('.',@Dim1,1)-1)
    set @Dim1 = substring(@Dim1,charindex(',',@Dim1,1)+1, len(@dim1))
    end
    
    select @str

    Thanks for your prompt reply.

    Thanks, Please Help People When they need..!!! Mark as answered if your problem is solved.

    • Marked as answer by BKomm Tuesday, May 9, 2017 5:31 PM
    • Unmarked as answer by BKomm Tuesday, May 9, 2017 5:31 PM
    Tuesday, May 9, 2017 5:31 PM
  • Declare @Dim1 Varchar(500)
    Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'
    
    DECLARE @myXML AS XML = N'<H><r>' + REPLACE(@Dim1, ',', '</r><r>') + '</r></H>'
     
    
     ;with mycte as (
    Select stuff(val,1,charindex('.',val),'')  val,rn FROM (
    SELECT Vals.id.value('.', 'NVARCHAR(50)') AS val
    ,row_number() Over(Order By getdate()) as rn
    FROM @myXML.nodes('/H/r') AS Vals(id) ) t
    )
    
    
     
    SELECT distinct  Stuff(( SELECT ',' + t2.val
               FROM mycte t2
             
              
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
      FROM mycte t1
    ;
     

    Thanks for your reply.

    Thanks, Please Help People When they need..!!! Mark as answered if your problem is solved.

    Tuesday, May 9, 2017 5:31 PM
  • Hello,

    from the above query if I need to get those values individually into different varaibles how is it possible?

    Like

    Declare @Dim1 Varchar(500) Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'

    @Val1 = 'WIRECENTER'

    @Val2 = 'Channel_Name'

    @Val3 = 'Speed'

    @Val4 = 'Tenure'

    How to Achieve this?


    Thanks, Please Help People When they need..!!! Mark as answered if your problem is solved.

    Tuesday, May 9, 2017 8:43 PM
  • declare @Val1 varchar(50),
    @Val2 varchar(50),
    @Val3 varchar(50),
    @Val4 varchar(50)
    
    Declare @Dim1 Varchar(500)
    Set @Dim1 = 'W.WIRECENTER,C.Channel_Name,X.Speed, Y.Tenure'
    
    DECLARE @myXML AS XML = N'<H><r>' + REPLACE(@Dim1, ',', '</r><r>') + '</r></H>'
     
    
     ;with mycte as (
    Select stuff(val,1,charindex('.',val),'')  val,rn FROM (
    SELECT Vals.id.value('.', 'NVARCHAR(50)') AS val
    ,row_number() Over(Order By getdate()) as rn
    FROM @myXML.nodes('/H/r') AS Vals(id) ) t
    )
    
    select @Val1=max(Case when rn=1 then val end) ,
     @Val2=max(Case when rn=2 then val end),
     @Val3=max(Case when rn=3 then val end),
     @Val4=max(Case when rn=4 then val end) 
    from mycte
    print @Val1
    print @Val2
    print @Val3
    print @Val4

    • Marked as answer by BKomm Wednesday, May 10, 2017 3:47 AM
    Tuesday, May 9, 2017 10:52 PM
    Moderator
  • Thanks a Lot... This worked

    Thanks, Please Help People When they need..!!! Mark as answered if your problem is solved.

    Wednesday, May 10, 2017 3:47 AM