Dynamically Build Replace (from table values)

• texpic

  SSCertifiable

  Points: 5887

  More actions

  October 29, 2018 at 7:27 am

  #376137

  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: 442467

  More actions

  October 29, 2018 at 11:27 am

  #2011564

  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: 442467

  More actions

  October 29, 2018 at 11:42 am

  #2011568

  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: 5887

  More actions

  October 29, 2018 at 12:26 pm

  #2011579

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