Post reply

Remove or Replace unwanted Characters from a table

  • May 14, 2020 at 10:58 pm

    #3751851

    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

  • May 14, 2020 at 11:15 pm

    #3751860

    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 ,,,,,,,,,'
                                           ,','
                                           ,'<>'
                                        )
                                ,'><'
                                ,''
                             )
                     ,'<>'
                     ,','
                  );

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 15, 2020 at 1:23 am

    #3751879

    Thanks Phil,

    Much appreciated.

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

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