SQL Server Performance

Need to remove trailing comma upon extracting one column from a table using DTS

Discussion in 'Getting Started' started by vinayshenoy, Jan 9, 2008.

  1. vinayshenoy New Member

    I have a requirement to copy one column from a table to be copied to a .lst file. When I do this, I get a trailing comma at the end. How can I get rid of this.
    Here is the data available in the file.
    I don't want the comma at the end. How can I do this....?
  2. Madhivanan Moderator

  3. vinayshenoy New Member

    Thanks for the reply guys. I did use the left function, I have two constraints here,
    1. I need to schedule this to execute automatically on a daily basis. The result should be put in a text file with all the ID's separated by comma. I'll then take this file & pass to Crystal report engine.
    2. When I run this in the query analyzer, I get the desired result, but for some reason I'm unable to use the same in DTS. It gives error regd Transformation is not proper.
    3. I tired using Job schedule from Agent, it gives me the file but it has many other information about scheduling. It does not give me only the data.
    Here's the code I used,
    DECLARE @Orders Varchar(8000)
    set NoCOUNT ON
    SET @Orders = ''
    SELECT @Orders = @Orders + Convert(varchar(10),trackingID) + ','
    FROM trackingItem where Lifecyclestate=2
    Print (Left(@orders,Len(@orders)-1)) --remove last comma)
    This works in query analyzer but not in DTS.
  4. Adriaan New Member

    Also think about better control-of-flow in your concatenation. You're probably adding each item with a comma behind it, whereas you should just start with the first item, then when you add other items you put a comma between the existing string and the new item.
    If you're using set-based syntax, use CASE for control-of-flow:
    DECLARE @string VARCHAR(100)

    SET @string = ''

    SELECT @string = @string + CASE WHEN LEN(@string) = 0 THEN '' ELSE ',' END + columnX
    FROM tableX
    WHERE ...........
    If you're currently using a cursor, note that the set-based version usually performs a lot better.
  5. FrankKalis Moderator

    That sort of depends on how you actually generate that list. Here's a version that doesn't require tweaking the final list:

    col1 VARCHAR(8)

    INSERT INTO note (col1) VALUES('Notes1')
    INSERT INTO note (col1) VALUES('Notes2')
    INSERT INTO note (col1) VALUES('Notes3')
    INSERT INTO note (col1) VALUES('Notes4')
    INSERT INTO note (col1) VALUES('Notes5')
    INSERT INTO note (col1) VALUES('Notes6')
    INSERT INTO note (col1) VALUES('Notes7')
    INSERT INTO note (col1) VALUES('Notes8')
    INSERT INTO note (col1) VALUES('Notes9')
    INSERT INTO note (col1) VALUES('Notes10')

    DECLARE @allnotes VARCHAR(8000)
    @allnotes = ISNULL( @allnotes + ', ', '' ) + col1

    DROP TABLE note
    Though it isn't DTS at all. [:)]
  6. Madhivanan Moderator

    Also you can use for xml
    From Frank's example
    col1 +','
    for xml path('')

Share This Page