Remove or Replace unwanted Characters from a table

  • Oscar_Boots

    Old Hand

    Points: 313

    Hi,

    I have some Tables that I want to transform in SSIS.

    Any occurences of more than one comma or other Character (|,/ etc..) should be removed leaving just one.

    This needs to be applied to the whole Table.

    Can anyone advise the best way to do this in SSIS?

    Thanks

  • Phil Parkin

    SSC Guru

    Points: 244578

    Use an ExecuteSQL task and do it in T-SQL using REPLACE with the standard trick. For example, for commas:

    SELECT REPLACE(
    REPLACE(
    REPLACE(
    ',,,,,,too many commas in this drivel ,,,,,,,,,,,,,,,,,,, some dodgy test here ,,,,, adjhakj ,,,,,,,,,'
    ,','
    ,'<>'
    )
    ,'><'
    ,''
    )
    ,'<>'
    ,','
    );

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Oscar_Boots

    Old Hand

    Points: 313

    Thanks Phil,

    Much appreciated.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply