Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Relace a string

Author  Topic 

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-13 : 11:16:15
hi
i have the following code for replacing a string.


SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF

-- Declare varibles
DECLARE
@SQLStmt NVARCHAR(MAX),
@FieldName NVARCHAR(50),
@StringReplacement NVARCHAR(200),
@StringToFind NVARCHAR(200),
@maxPK INT,
@pk INT,
@Quote NVARCHAR(1),
@WorkingNamingTable NVARCHAR(256),
@WorkingDataloadFile3 NVARCHAR(256)

SELECT @WorkingNamingTable = ?
SELECT @WorkingDataloadFile3 = ?

--Make sure the temp table has been dropped
--Only for use when testing in SQL mgmt studio
--BEGIN
-- DROP TABLE #StringReplace
--END
SET @Quote = '"'
-- Create temp table
SELECT @SQLStmt = '
Select PK_ID
INTO #StringReplace
FROM @WorkingNamingTable

-- Get the max ID in the table
-- table is new each time so do not need to worry about
-- ID not starting at 1
Select @maxPK = MAX(PK_ID) FROM #StringReplace
SET @pk = 1



-- Loop thru the table
WHILE @pk <= @maxPK
BEGIN

-- Preform replacement (note we are replacing in the whole field not just the end)
SELECT @FieldName = Field from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringReplacement = StringReplacement from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringToFind = StringToFind from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)


UPDATE ' + @WorkingDataloadFile3 + '
SET ' + @FieldName + ' = ' + ' " " + ' + @FieldName + ' + " " FROM '+ @WorkingDataloadFile3 +'
WHERE ' + @FieldName + ' IS NOT NULL

UPDATE ' + @WorkingDataloadFile3 + '
SET ' + @FieldName + ' = REPLACE(' + @FieldName + ',' + @Quote + @StringToFind + @Quote + ',' + @Quote + @StringReplacement + @Quote + ')
FROM ' + @WorkingDataloadFile3 + '
WHERE ' + @FieldName + ' IS NOT NULL


Select @pk = @pk + 1

END

SET @pk = 1

-- Loop thru the table
WHILE @pk <= @maxPK
BEGIN

UPDATE '+ @WorkingDataloadFile3 +'
SET ' + @FieldName + ' = LTRIM(RTRIM(' + @FieldName + ')) FROM @WorkingDataloadFile3
WHERE ' + @FieldName + ' IS NOT NULL

Select @pk = @pk + 1
END'

EXECUTE(@SQLStmt)

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


it does not seem to update the field. can anone see anything wrong with code

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-13 : 12:13:42
Add PRINT statements before the UPDATE for each of the variables. You might have a NULL issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 04:26:20
i have put
PRINT (@StringToFind)
PRINT (@StringReplacement)

before the updates but its not showing up anything. any ideas why?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-14 : 04:52:18
You are not displaying enough information to debug it, instead of

PRINT (@StringToFind)

at the least I would use:

PRINT ''@pk='' + CONVERT(varchar(20), @pk)
+ '', @FieldName='' + COALESCE(@FieldName, ''[NULL]'')


This code:

Select PK_ID
INTO #StringReplace
FROM @WorkingNamingTable


Also before

EXECUTE(@SQLStmt)

put

PRINT '@SQLStmt=' + COALESCE(@SQLStmt, '[NULL]')
EXECUTE(@SQLStmt)

and try executing whatever SQL is displayed (I would put BEGIN TRANSACTION and ROLLBACK around it, so that it doesn't update anything and you can just test that it runs OK / the syntax has no errors etc.)

Last point:

inside your SQLStmt string: I don't think?? that an @TemporaryTable inside the SQL String as I don't think the @WorkingNamingTable in outer code is in scope, is it?the
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 05:11:53
i suppose my bigest problem is this is not my code and im not used to t sql.

i have two tables WorkingNamingTable and WorkingDataloadFile3

in the WorkingNamingTable i have the following

StringTOFind Stringreplacment field
N Ap securitname
/dev securityname

and i want NAP TO BE UPDATE UNDER THE COULMN SECURITYNAME in theWorkingDataloadFile3.

would there be a problem replacing Nap with blank.?
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 05:19:59
and there is not id the matches from 1 table to the other
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-14 : 06:02:00
quote:
Originally posted by rjhe22

would there be a problem replacing Nap with blank.?



No, unless there is a trigger that rolls back the transaction if that column is blank. Seems very unlikely.

You need to use PRINT statements in your dynamically generated SQL string to display what actual SQL is being executed.

You can then try that SQL manually to see what happens. I expect you will get a syntax error, you will also get a message indicating what the error is, so you can fix it in your working-code, run it again until it works, and then retro-fit the changes you made back into the original code that generates the dynamic SQL.

If you don't understand the error you get you can post the SQL and error message here and I am sure someone will help.

At the very least make this change:

PRINT '@SQLStmt=' + COALESCE(@SQLStmt, '[NULL]')
EXECUTE(@SQLStmt)

post the SQL which is displayed, and also execute that SQL like this:

BEGIN TRANSACTION
GO
[i]your SQL here/i]
ROLLBACK

and also post any error message you get from that.
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 06:12:19
I put ththis line PRINT '@SQLStmt=' + COALESCE(@SQLStmt, '[NULL]')
in before
EXECUTE(@SQLStmt)

at the end of the script and i got this
@SQLStmt=[NULL]
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 06:56:41
cif i leave this two lines in
SELECT @WorkingNamingTable = ?
SELECT @WorkingDataloadFile3 = ?
i get the following error message
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '?'.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-14 : 07:04:13
OK, well that's your first problem then and explains why nothing happens when you run the routine.

Something in the code that constructs the @SQLStmt is NULL. If you concatenate something which is NULL to something else then the result is always NULL.

You could use COALESCE to find them, e.g. change:

...
SET ' + @FieldName + ' = ' + ' " " + ' + @FieldName + ' + " " FROM '+ @WorkingDataloadFile3 +'
...

to

...
SET ' + COALESCE(@FieldName, '@FieldName_NULL') + ' = ' + ' " " + ' + COALESCE(@FieldName, '@FieldName_NULL') + ' + " " FROM '+ COALESCE(@WorkingDataloadFile3, '@WorkingDataloadFile3_NULL') +'
...

This will raise syntax errors if anything attempts to run it, but you should be able to see what the problem is in the SQL that is output by the PRINT statement.

You need to do this for every string concatenation (which you think MIGHT be NULL). You can start with the most obvious, and keep repeating the replaces until the output from the PRINT statement stops being "[NULL]"
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-14 : 07:06:31
quote:
Originally posted by rjhe22

cif i leave this two lines in
SELECT @WorkingNamingTable = ?
SELECT @WorkingDataloadFile3 = ?
i get the following error message
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '?'.


I assumed you were replacing those with some valid parameters. The syntax

SELECT @WorkingNamingTable = ?

doesn't mean anything - its just an indication that you need to provided a value and as I indicated before

INTO #StringReplace
FROM @WorkingNamingTable

is not valid. What you probably mean is

INTO #StringReplace
FROM ' + @WorkingNamingTable + '

if that is what you meant you need to replace the other instances, and if there is a risk that @WorkingNamingTable might be NULL you need to use COALESCE to give you a dummy value when it is null, for debugging purposes.
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 10:16:20
I CANT GET IT TO WORK NO MATTER WHAT I DO.

WOULD ANYONE NO A CLEANER OR BETTER WAY TO DO THIS.
ANY CODE WOULD BE GREAT
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 11:37:26
IM JUST STRIPPING OUT THE SELECT AND UPDATE PART
	SELECT @FieldName = Field from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringReplacement = StringReplacment from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringToFind = StringToFind from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)


UPDATE ' + @WorkingDataloadFile3 + '
SET ' + @FieldName + ' = ' + ' " " + ' + @FieldName + ' + " " FROM '+ @WorkingDataloadFile3 +'
--WHERE ' + @FieldName + ' IS NOT NULL


how does it no what field in @WorkingDataloadFile3 table to update.

we name the slect fields from the @WorkingNamingTable.

am i reading it wrong?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-14 : 12:15:08
Also use QUOTENAME function just in case @FieldName contains illegal characters.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 12:35:03
DONT THINK THATS THE PROBLEM BUT I WILL TRY THAT AS WELL.
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 12:49:55
CAN I TEST IT LIKE THIS
SET @FieldName ='SecurityName'
SET @StringReplacement ='BL'
SET @StringToFind ='N AP'
SET @Quote = '"'
SET @maxPK = '1'
SET @pk = '1'



AND IF SO WHERE CAN I PUT THAT CODE WHEN I PUT IT IN AFTER THE DECLARE I GET THE FLOWING ERRORS

Must declare the scalar variable "@pk".
Msg 137, Level 15, State 1, Line 41
Must declare the scalar variable "@pk".
Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@pk".
Msg 1087, Level 15, State 2, Line 48
Must declare the table variable "@WorkingDataloadFile3".
Msg 137, Level 15, State 2, Line 51
Must declare the scalar variable "@pk".

EVEN THOUGH THEY ALL ARE DELCARED

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-14 : 12:58:19
Show us the entire code again that is now showing the syntax errors.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-14 : 13:02:05
[code]SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF

-- Declare varibles
DECLARE

@SQLStmt NVARCHAR(MAX),
@FieldName NVARCHAR(50),
@StringReplacement NVARCHAR(200),
@StringToFind NVARCHAR(200),
@maxPK INT,
@pk INT,
@Quote NVARCHAR(1),
@WorkingNamingTable NVARCHAR(256),
@WorkingDataloadFile3 NVARCHAR(256)

SELECT @WorkingNamingTable = 'dbo.SSCLXWorkingNamingTable'
SELECT @WorkingDataloadFile3 = 'dbo.SSCLXWorkingDataloadFile3'
SET @FieldName ='SecurityName'
SET @StringReplacement ='BL'
SET @StringToFind ='N AP'
SET @Quote = '"'
SET @maxPK = '1'
SET @pk = '1'

--Make sure the temp table has been dropped
--Only for use when testing in SQL mgmt studio
--BEGIN
-- DROP TABLE #StringReplace
--END

-- Create temp table
SELECT @SQLStmt = '



Select PK_ID
INTO #StringReplace
FROM @WorkingNamingTable

-- Get the max ID in the table
-- table is new each time so do not need to worry about
-- ID not starting at 1
Select @maxPK = MAX(PK_ID) FROM #StringReplace
SET @pk = 1



-- Loop thru the table
WHILE @pk <= @maxPK
BEGIN

-- Preform replacement (note we are replacing in the whole field not just the end)
--SELECT @FieldName = Field from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
--SELECT @StringReplacement = StringReplacement from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
--SELECT @StringToFind = StringToFind from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)


UPDATE ' + @WorkingDataloadFile3 + '
SET ' + @FieldName + ' = ' + ' " " + ' + @FieldName + ' + " " FROM '+ @WorkingDataloadFile3 +'
WHERE ' + @FieldName + ' IS NOT NULL

UPDATE ' + @WorkingDataloadFile3 + '
SET ' + @FieldName + ' = REPLACE(' + @FieldName + ',' + @Quote + @StringToFind + @Quote + ',' + @Quote + @StringReplacement + @Quote + ')
FROM ' + @WorkingDataloadFile3 + '
WHERE ' + @FieldName + ' IS NOT NULL


Select @pk = @pk + 1

END

SET @pk = 1

-- Loop thru the table
WHILE @pk <= @maxPK
BEGIN

UPDATE '+ @WorkingDataloadFile3 +'
SET ' + @FieldName + ' = LTRIM(RTRIM(' + @FieldName + ')) FROM @WorkingDataloadFile3
WHERE ' + @FieldName + ' IS NOT NULL

Select @pk = @pk + 1
END'

EXECUTE(@SQLStmt)

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
[/code]

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-14 : 13:54:26
The errors are because you've got a missing or extra single quote. The issue starts at line 33: SELECT @SQLStmt. You've got a single quote but no ending quote.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-14 : 20:27:23
You've got no PRINT statements in that code?

And no COALESCE to change any NULLs that accidentally get into any of the variables into something that will raise an error but at least enable you to see where the problem is.

You haven't sorted out:

Select PK_ID
INTO #StringReplace
FROM @WorkingNamingTable

as per my earlier message
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2015-04-15 : 04:30:12
Hi

I hAVE PUT THE PRINT STATEMENT IN AND THE COALESCE BUT ALL IT DOES IT PRINT
@SQLStmt=[NULL]

IF I CHANGE THE NULL IN THE PRINT PART TO TEST THEN IT PRINTS
@SQLStmt=[TEST]

THIS IS MY UPDATED CODE IM NOT SURE IF I DID THE COALESCE RIGHT
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
BEGIN TRANSACTION
GO
-- Declare varibles
DECLARE
@SQLStmt NVARCHAR(MAX),
@FieldName NVARCHAR(50),
@StringReplacement NVARCHAR(200),
@StringToFind NVARCHAR(200),
@maxPK INT,
@pk INT,
@Quote NVARCHAR(1),
@WorkingNamingTable NVARCHAR(256),
@WorkingDataloadFile3 NVARCHAR(256)

SELECT @WorkingNamingTable = "dbo.SSCLXWorkingNamingTable"
SELECT @WorkingDataloadFile3 = "dbo.SSCLXWorkingDataloadFile3"

--Make sure the temp table has been dropped
--Only for use when testing in SQL mgmt studio
--BEGIN
-- DROP TABLE #StringReplace
--END
SET @Quote = '"'
-- Create temp table
SELECT @SQLStmt = '
Select PK_ID
INTO #StringReplace
FROM ' + @WorkingDataloadFile3 + '

-- Get the max ID in the table
-- table is new each time so do not need to worry about
-- ID not starting at 1
Select @maxPK = MAX(PK_ID) FROM #StringReplace
SET @pk = 1



-- Loop thru the table
WHILE @pk <= @maxPK
BEGIN

-- Preform replacement (note we are replacing in the whole field not just the end)
SELECT @FieldName = Field from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringReplacement = StringReplacement from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
SELECT @StringToFind = StringToFind from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)


UPDATE ' + @WorkingDataloadFile3 + '
--SET ' + @FieldName + ' = ' + ' " " + ' + @FieldName + ' + " " FROM '+ @WorkingDataloadFile3 +'
SET ' + COALESCE(@FieldName, '@FieldName_NULL') + ' = ' + ' " " + ' + COALESCE(@FieldName, '@FieldName_NULL') + ' + " " FROM '+ COALESCE(@WorkingDataloadFile3, '@WorkingDataloadFile3_NULL') +'
WHERE ' + @FieldName + ' IS NOT NULL

UPDATE ' + @WorkingDataloadFile3 + '
SET ' + COALESCE(@FieldName, '@FieldName_NULL') + ' = REPLACE(' + COALESCE(@FieldName, '@FieldName_NULL') + ',' + @Quote + COALESCE(@StringToFind, '@StringToFind_NULL') + @Quote + ',' + @Quote + COALESCE(@StringReplacement, '@StringReplacement_NULL') + @Quote + '))
FROM ' + @WorkingDataloadFile3 + '
WHERE ' + @FieldName + ' IS NOT NULL


Select @pk = @pk + 1

END

SET @pk = 1

-- Loop thru the table
WHILE @pk <= @maxPK
BEGIN

UPDATE '+ @WorkingDataloadFile3 +'
SET ' + COALESCE(@FieldName, '@FieldName_NULL') + ' = LTRIM(RTRIM(' + COALESCE(@FieldName, '@FieldName_NULL') + ')) FROM @WorkingDataloadFile3
WHERE ' + @FieldName + ' IS NOT NULL

Select @pk = @pk + 1
END'
PRINT '@SQLStmt=' + COALESCE(@SQLStmt, '[NULL]')

EXECUTE(@SQLStmt)

ROLLBACK
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -