Relace a string - SQL Server Forums

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/

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?

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

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.?

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

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.

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]

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 '?'.

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 +'
...


...
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]"

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.

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

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?

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

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.

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

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/

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]

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/

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

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources