October 29, 2018 at 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)
October 29, 2018 at 11:27 am
texpic - Monday, October 29, 2018 7:27 AMTwo 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?
October 29, 2018 at 11:42 am
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];
October 29, 2018 at 12:26 pm
Lynn, that is exactly what I was needing. Thank you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply