Dynamically Build Replace (from table values)

  • texpic

    SSCertifiable

    Points: 5882

    Two questions:

    When I cut/paste code from the SQL query window into this area I always get spaces.  Is there a way to avoid the spaces?

    What I'm trying to do is dynamically build and execute a replace statement built from a table.  There are three rows in the table #Test but when I try to build the dynamic statement (@SQL) I'm only getting one row.  Thanks for your help!


    --want to replace names in this table with '' if they are in #test table

    CREATE TABLE #Target (RecordId int, AllNames varchar(30))

    INSERT INTO #Target

    SELECT 1, 'Ann Barb Sue Joe' UNION ALL

    SELECT 2, 'Ann Barb Sue'

    CREATE TABLE #Test (MyNames varchar(30))

    INSERT INTO #Test

    SELECT 'Ann' UNION ALL

    SELECT 'Barb' UNION ALL

    SELECT 'Sue'

    --this manually builds the replace code

    SELECT 'UPDATE #Target SET AllNames = REPLACE(AllNames,''' + MyNames + ''', '''')' FROM #Test

    UPDATE #Target SET AllNames = REPLACE(AllNames,'Ann', '')

    UPDATE #Target SET AllNames = REPLACE(AllNames,'Barb', '')

    UPDATE #Target SET AllNames = REPLACE(AllNames,'Sue', '')

    --this is only giving me one line of code, want a replace for each record (three of them)

    DECLARE @Sql varchar(1000)

    SELECT @Sql = 'UPDATE #Target SET AllNames = REPLACE(AllNames,''' + MyNames + ''', '''')' FROM #Test

    PRINT(@Sql)

    --EXEC(@Sql)

  • Lynn Pettis

    SSC Guru

    Points: 442332

    texpic - Monday, October 29, 2018 7:27 AM

    Two questions:

    When I cut/paste code from the SQL query window into this area I always get spaces.  Is there a way to avoid the spaces?

    What I'm trying to do is dynamically build and execute a replace statement built from a table.  There are three rows in the table #Test but when I try to build the dynamic statement (@SQL) I'm only getting one row.  Thanks for your help!


    --want to replace names in this table with '' if they are in #test table

    CREATE TABLE #Target (RecordId int, AllNames varchar(30))

    INSERT INTO #Target

    SELECT 1, 'Ann Barb Sue Joe' UNION ALL

    SELECT 2, 'Ann Barb Sue'

    CREATE TABLE #Test (MyNames varchar(30))

    INSERT INTO #Test

    SELECT 'Ann' UNION ALL

    SELECT 'Barb' UNION ALL

    SELECT 'Sue'

    --this manually builds the replace code

    SELECT 'UPDATE #Target SET AllNames = REPLACE(AllNames,''' + MyNames + ''', '''')' FROM #Test

    UPDATE #Target SET AllNames = REPLACE(AllNames,'Ann', '')

    UPDATE #Target SET AllNames = REPLACE(AllNames,'Barb', '')

    UPDATE #Target SET AllNames = REPLACE(AllNames,'Sue', '')

    --this is only giving me one line of code, want a replace for each record (three of them)

    DECLARE @Sql varchar(1000)

    SELECT @Sql = 'UPDATE #Target SET AllNames = REPLACE(AllNames,''' + MyNames + ''', '''')' FROM #Test

    PRINT(@Sql)

    --EXEC(@Sql)

    Could you post what you are expecting the dynamic SQL to look like please?

  • Lynn Pettis

    SSC Guru

    Points: 442332

    And you might want to try this:

    --want to replace names in this table with '' if they are in #test table
    if object_id('tempdb..#Target') is not null
    drop TABLE #Target;

    if object_id('tempdb..#Test') is not null
    drop TABLE #Test;

    CREATE TABLE #Target (RecordId int, AllNames varchar(30))
    INSERT INTO #Target
    SELECT 1, 'Ann Barb Sue Joe' UNION ALL
    SELECT 2, 'Ann Barb Sue' ;

    CREATE TABLE #Test (MyNames varchar(30))
    INSERT INTO #Test
    SELECT 'Ann' UNION ALL
    SELECT 'Barb' UNION ALL
    SELECT 'Sue';

    --this manually builds the replace code
    SELECT 'UPDATE #Target SET AllNames = REPLACE(AllNames,''' + MyNames + ''', '''')' FROM #Test

    UPDATE #Target SET AllNames = REPLACE(AllNames,'Ann', '')

    UPDATE #Target SET AllNames = REPLACE(AllNames,'Barb', '')

    UPDATE #Target SET AllNames = REPLACE(AllNames,'Sue', '')

    --this is only giving me one line of code, want a replace for each record (three of them)
    DECLARE @Sql nvarchar(max)

    --SELECT @Sql = 'UPDATE #Target SET AllNames = REPLACE(AllNames,''' + MyNames + ''', '''')' FROM #Test

    SELECT @Sql = stuff((select N'UPDATE #Target SET AllNames = REPLACE(AllNames,''' + MyNames + ''', '''');' + nchar(13) + nchar(10) FROM #Test for xml path(''),TYPE).value('./text()[1]','nvarchar(max)'),1,0,'')

    PRINT(@Sql)

    --EXEC sys.sp_executesql @Sql;

    select * from [#Target];

  • texpic

    SSCertifiable

    Points: 5882

    Lynn, that is exactly what I was needing.  Thank you.

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

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