Post reply

Dynamic String Manipulation

  • September 15, 2015 at 10:12 am

    #321244

    Top of the day to you all,

    I have the following data in a table;

    Create Table #Table (Column1 Varchar(1000))

    INSERT INTO #Table

    Select 'Execute Procedure @param1 = 100, @param2 = 1000, @param3 = ''longtext'' '

    UNION ALL

    Select 'Execute Procedure @param1 = 10, @param2 = 1000, @param3 = ''longtext'' '

    UNION ALL

    Select 'Execute Procedure @param1 = 100000, @param2 = 1000, @param3 = ''longtext'' '

    Select * from #Table

    I want to get rid of @Param1 in every row of the table so my final results look like this.

    Create Table #FinalTable (Column1 Varchar(1000))

    INSERT INTO #FinalTable

    Select 'Execute Procedure @param2 = 1000, @param3 = ''longtext'' '

    UNION ALL

    Select 'Execute Procedure @param2 = 1000, @param3 = ''longtext'' '

    UNION ALL

    Select 'Execute Procedure @param2 = 1000, @param3 = ''longtext'' '

    Select * from #FinalTable

    The tricky part is that the @param1 value length varies so a straight forward substring or replace function won't work.

    Please how can I achieve this? Thank you

  • September 15, 2015 at 10:34 am

    #1826815

    Try this.

    select column1 ,

    stuff(column1, charindex('@param1', column1),

    charindex('@param2', column1) - charindex('@param1', column1),

    '')

    from #Table;

    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

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

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