none
New line after delimiter help in SSMS

    Question

  • Hello,

    I am using below code in SQl Server management studio 2008 R2 (Online friend VT helped me to get this code, many thanks to him once agian).

    select substring([Team Members],patindex('%[a-z]%',[Team Members]),len([Team Members])-patindex('%[a-z]%',[Team Members])+1) as [Team Member]
    from(
    	select	replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace([Team Members],';#1;#',';'),';#2;#',';'),';#3;#',';'),';#4;#',';'),';#5;#',';'),';#6;#',';'),';#7;#',';'),';#8;#',';'),';#9;#',';'),';#0;#',';'),';#','')[Team Members] FROM [TEMP].[dbo].[ListTable]
        )rs

    When i execute above code, i am getting below result.

    Team Members

    -----------------

    ABC;XYZ

    but, i need, like...

    Team Members

    ----------------------

    ABC

    XYZ

    Could please help me by providing suitable solution, many thanks in advance.

    Kind Regards

    • Moved by Tom PhillipsModerator Monday, April 16, 2012 2:23 PM TSQL question (From:SQL Server Database Engine)
    Friday, April 13, 2012 9:43 AM

Answers

  • Here is one way to do it, using an XML based approach.  The benefit of this way is that if this is a one-time need, you don't need to create a user defined function to do it.  Most people eventually also add the popular SPLIT function as a UDF, because sooner or later you'd have another reason to use one.

    The SQL below is oversimplified and separated into many separate steps, many of which could be combined into just a couple more complex statements, but for demonstration purposes, it doesn't hurt to separate them from each other.    You would also need a CROSS APPLY if you were trying to directly merge this in with another query, but it doesn't look like that's what you needed to do for this problem.

    Declare @InString Varchar(Max)
    Set @InString = '5;#ABC;#7;#XYZ'
    
    Set @InString = Replace(@InString, ';#', '</V><V>')
    Set @InString = '<V>' + @InSTring + '</V>'
    Select @InString  /* Optional, just to show contents */
    
    Declare @InStringXML xml
    Set @InStringXML = Cast(@InString as XML)
    Select @InStringXML  /* Optional, just to show contents */
    
    Select * From
    	(
    	Select DLTab.DLVal.value('.', 'varchar(8000)') as DLCol from @InStringXML.nodes('V') DLTab(DLVal)
     	) as DQ
     where DLCOL like '%[abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ]%'

    Monday, April 16, 2012 4:48 PM

All replies

  • Does this help you:

    http://codehighstreet.com/Snippets/parsing_comma_separated_values_into_table_in_sql_server.aspx

    If your problem is different, it would help if you provided a few rows of the temptable you use in the above query


    Dr. Nico Jacobs, SQL Server BI trainer @ U2U.net

    Friday, April 13, 2012 10:04 AM
  • Hi,

    If you need to insert a carriage return line feed you can add a variable and add the variable to the correct place in the string kind of like this.

     DECLARE @CRLF AS CHAR(2) = CHAR(13) + CHAR(10)
     PRINT ('SELECT FirstTeamMember AS FirstLine' + @CRLF + 'SELECT SecondTeamMember AS SecondLine' )
     GO


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, April 13, 2012 10:13 AM
  • Hi

    If i remember correctly the following code will do the job.see the e.g

    --create this function 1st
    CREATE  function [dbo].[listtocsv](@l varchar(200))
    returns @csv table(strdata varchar(20))
    as
    begin
    	
    	declare @xml xml
    	set @xml = '<root><record>' + replace(@l,';','</record><record>') +
     '</record></root>'
     
    	insert into @csv(strdata)
    	
    select * from(
    select
      t.value('.','varchar(150)') as [items]
    from @xml.nodes('//root/record') as a(t)) data
    WHERE patindex('%[a-z]%',[items])>=1
    	return
    end
    create table #list(Members varchar(50))
    insert into #list values('5;#ABC;#7;#XYZ'),('5;#ABC;#237;#XYZ'),('5;#RTY;#237;#ASS;#237;JKL')
    select c.*,RS.DATA
    from(
    	select Members AS DATA,	replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace([Members],';#1;#',';'),';#2;#',';'),';#3;#',';'),';#4;#',';'),';#5;#',';'),';#6;#',';'),';#7;#',';'),';#8;#',';'),';#9;#',';'),';#0;#',';'),'#','')[Members]FROM #list
        )rs
        cross apply listtocsv(substring([Members],patindex('%[a-z]%',[Members]),len([Members])-patindex('%[a-z]%',[Members])+1)) c
    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, April 13, 2012 10:28 AM
  • Hi, you may try something like this (quick and only for less then 10 Members), BR Stephan

    IF OBJECT_ID('tempdb.dbo.##ListTable', 'U') IS NOT NULL DROP TABLE dbo.##ListTable;
    IF OBJECT_ID('tempdb.dbo.##HelpTable', 'U') IS NOT NULL DROP TABLE dbo.##HelpTable;
      
    CREATE TABLE dbo.##ListTable([Team Members] varchar(max))
    CREATE TABLE dbo.##HelpTable([Team Members] varchar(max))
        
    insert dbo.##ListTable SELECT  'aaa;#1;#bbb;#2;#ccc;#3;#ddd;#4;#eee;#5;#fff;#6;#ggg;#7;#hhh;#8;#iii;#9;#'
    select * from ##ListTable
    declare @s as varchar(max) -- working string
    declare @i as int -- counter
    set @s = (select top(1) * from ##ListTable)
    WHILE (NOT PATINDEX('%;#%;#%', @s) = 0)
    BEGIN
       set @i = PATINDEX('%;#%;#%', @s)
       insert into dbo.##HelpTable select LEFT(@s, @i-1)
       set @s = Substring(@s, @i+5,99999999)
       IF (@i = 0)
    	  BREAK
       ELSE
          CONTINUE
    END
    select *from ##HelpTable
    DROP TABLE dbo.##ListTable
    DROP TABLE dbo.##HelpTable
    Friday, April 13, 2012 11:17 AM
  • Many many thanks for the replies.

    Main problem for me is (i am not big SQl guy), where do i need to insert my Column name, which is "Team Member" and Table name which is "List Table". and other columns are "Team Lead", "Project".

    If you don't mind guys could please insert my column name and table name accordingly.

    Once again many thanks.

    Kind Regards

    Friday, April 13, 2012 11:45 AM
  • Hello,

    Could please let me know where must i apply my column name in the above code. Column name is "Team Members", and the table name is "ListTable".

    Thank you.

    Kind Regards

    Monday, April 16, 2012 7:40 AM
  • I'm guessing the answer to your question will be a "Split" function.  However, to be sure, can you either restate your original issue (the one you referenced where "VT" gave you an answer, or provide a hyperlink to the original thread (it's not clear if this was also within MSDN) where you got your original query?

    I'm guessing (still guessing) your data originates as (for example): "al;#1;#bob;#2;#" etc, delimited by ';#' instead of the more common comma, but still, a little more detail will help someone here give you a thorough answer.


    EDIT:  Actually, here is the original question:  RP Reddy's original question, answered by V.VT.  And yes, looks like a split function will take care of this, possibly even to bypass the interim solution and go straight to querying the split output to only include the non-number rows.
    • Edited by johnqflorida Monday, April 16, 2012 4:30 PM Added URL to original question.
    Monday, April 16, 2012 4:27 PM
  • Here is one way to do it, using an XML based approach.  The benefit of this way is that if this is a one-time need, you don't need to create a user defined function to do it.  Most people eventually also add the popular SPLIT function as a UDF, because sooner or later you'd have another reason to use one.

    The SQL below is oversimplified and separated into many separate steps, many of which could be combined into just a couple more complex statements, but for demonstration purposes, it doesn't hurt to separate them from each other.    You would also need a CROSS APPLY if you were trying to directly merge this in with another query, but it doesn't look like that's what you needed to do for this problem.

    Declare @InString Varchar(Max)
    Set @InString = '5;#ABC;#7;#XYZ'
    
    Set @InString = Replace(@InString, ';#', '</V><V>')
    Set @InString = '<V>' + @InSTring + '</V>'
    Select @InString  /* Optional, just to show contents */
    
    Declare @InStringXML xml
    Set @InStringXML = Cast(@InString as XML)
    Select @InStringXML  /* Optional, just to show contents */
    
    Select * From
    	(
    	Select DLTab.DLVal.value('.', 'varchar(8000)') as DLCol from @InStringXML.nodes('V') DLTab(DLVal)
     	) as DQ
     where DLCOL like '%[abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ]%'

    Monday, April 16, 2012 4:48 PM