none
Remove Comma from String RRS feed

  • Question

  • Hello,

    I have a values in a column  like this - SAS,No,1,136.13 GB,Online,Ok,DELL(tm),Not Applicable,6XM3MGN0,YS0D,

    I am taking that column and parsing it to multiple columns using comma as delimiter.

    But I want to remove the comma from this  part only - 1,136.13 GB and feed it to one column like this 1136.13 GB.

    I am trying to use derived column but unable to find expression that will help me with this.

    How can I achieve this?

    Friday, June 14, 2019 8:42 PM

All replies

  • What data source are you using, MSSQLServer?

    me

    Friday, June 14, 2019 9:06 PM
  • yes its SQL Server datasource
    Friday, June 14, 2019 9:11 PM
  • You can create a stored proc that looks like this
    DECLARE @S VARCHAR(MAX),
            @Split CHAR(1),
            @X XML;
    
    SELECT @S = 'SAS,No,1,136.13 GB,Online,Ok,DELL(tm),Not Applicable,6XM3MGN0,YS0D,',
           @Split = ',';
    
    SELECT @X = CONVERT(XML, ' <root> <myvalue>' + REPLACE(@S, @Split, '</myvalue> <myvalue>') + '</myvalue>   </root> ');
    
    SELECT T.c.value('(/root/myvalue)[1]', 'VARCHAR(20)'), --retrieve index 1 only, which is the 'ab'
           T.c.value('(/root/myvalue)[2]', 'VARCHAR(20)'),
           T.c.value('(/root/myvalue)[3]', 'VARCHAR(20)') + T.c.value('(/root/myvalue)[4]', 'VARCHAR(20)'),
           T.c.value('(/root/myvalue)[5]', 'VARCHAR(20)'),
           T.c.value('(/root/myvalue)[6]', 'VARCHAR(20)'),
           T.c.value('(/root/myvalue)[7]', 'VARCHAR(20)'),
           T.c.value('(/root/myvalue)[8]', 'VARCHAR(20)'),
           T.c.value('(/root/myvalue)[9]', 'VARCHAR(20)'),
           T.c.value('(/root/myvalue)[10]', 'VARCHAR(20)')
    FROM @X.nodes('/root') T(c);


    me

    Friday, June 14, 2019 9:25 PM
  • I am not looking for stored proc solution. I am using SSIS. I know its possible with derived columns. But I need help figuring out expression .
    Friday, June 14, 2019 10:51 PM
  • You can use TOKEN to do that in Derived Column, the expression will look like this:

    TOKEN([Column],",",1)


    me


    Friday, June 14, 2019 11:09 PM
  • I figured out the parsing. But there is a problem. 

    I want to remove the comma from this  part only - 1,136.13 GB and feed it to one column like this 1136.13 GB.

    Friday, June 14, 2019 11:15 PM
  • Hi AmitSSRS,

    We could also use the following expression:

    TOKEN(REPLACE([Column 0],"1,136.13 GB","1136.13 GB"),",",1)

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 17, 2019 7:05 AM
  • Hello,

    I have a values in a column  like this - SAS,No,1,136.13 GB,Online,Ok,DELL(tm),Not Applicable,6XM3MGN0,YS0D,

    I am taking that column and parsing it to multiple columns using comma as delimiter.

    But I want to remove the comma from this  part only - 1,136.13 GB and feed it to one column like this 1136.13 GB.

    I am trying to use derived column but unable to find expression that will help me with this.

    How can I achieve this?

    Hi Amit,

    You can try the following in SSIS:

    replace(substring(Column1,findstring(Column1,"No",1)+3,findstring(Column1,"GB",1)-(findstring(Column1,"No",1)+4)),',','')


    Monday, June 17, 2019 7:44 AM