July 7, 2008 at 11:26 am
I need to write some logic using SS2K that will remove certain characters in a string. Right now, the characters to be dropped are single quotes and exclamation points, so something like this is required:
DECLARE @StringToReturn VARCHAR(1000)
DECLARE@StringToReplaceCHAR(1)
SET@StringToReturn = 'John ! O''Connor'
SET@StringToReplace= '''' --this is a pair of single quotes
SET@StringToReturn = REPLACE(@StringToReturn, @StringToReplace, '')
SET@StringToReplace = '!'
SET@StringToReturn = REPLACE(@StringToReturn, @StringToReplace, '')
SELECT@StringToReturn
This works fine. The problem is, I have a table of characters to be dropped, so I figured the best way to do this would be to build a function that checked this table, and did the replace using a WHILE loop. Here's the DDL for the table:
CREATE TABLE [SpotscInvalidCharacters] (
[InvalidCharacter] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InvalidCharacterAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_SpotscInvalidCharacters] PRIMARY KEY CLUSTERED
(
[InvalidCharacter]
) ON [PRIMARY]
) ON [PRIMARY]
And here's how to populate it:
Insert Into SpotscInvalidCharacters
select'''', 'D'union
select'!', 'D'
Here's the function I've written:
CREATE FUNCTION dbo.fnSpotsInvalidCharacters (@StringToValidate VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @StringToReturnVARCHAR(1000)
DECLARE@StringToFindVARCHAR(100)
DECLARE@StringToValidateLengthINT
DECLARE @StringPositionINT
DECLARE@TestStringLengthINT
DECLARE@CounterSMALLINT
DECLARE@ItemsToCountSMALLINT
SET@StringToValidate = RTRIM(LTRIM(@StringToValidate))
SET@StringToValidateLength= LEN(@StringToValidate)
SET@StringPosition= 1
SET@StringToFind= ''
SET@StringToReturn = @StringToValidate
SET@Counter = 0
SELECT@ItemsToCount = COUNT(*)
FROMOdysseyNH.dbo.SpotscInvalidCharacters
WHEREInvalidCharacterAction = 'D'
SELECT TOP 1 @StringToFind = InvalidCharacter
FROMOdysseyNH.dbo.SpotscInvalidCharacters
WHEREInvalidCharacterAction = 'D'
ANDInvalidCharacter > @StringToFind
ORDER BY
InvalidCharacter
WHILE @Counter < @ItemsToCount
BEGIN
SET Counter = @Counter + 1
SET@StringToReturn = REPLACE(@StringToReturn, @StringToFind, '')
SELECT TOP 1 @StringToFind = InvalidCharacter
FROMSpotscInvalidCharacters
WHEREInvalidCharacterAction = 'D'
ANDInvalidCharacter > @StringToFind
ORDER BY
InvalidCharacter
END
RETURN@StringToReturn
END
This doesn't work. My first thought was that it was the single quote I was trying to drop, but it doesn't work for the exclamation point either. When I add the following statement
SET@StringToReturn = @StringToFind
I can see that it's finding the correct values to drop, but it just doesn't seem to be doing the replace, and I can't figure out why.
I'm not wedded to this particular solution, so if anyone has any thoughts on how to do this differently, I'm all for it. But can anyone tell me why my code isn't working?
Thanks,
Mattie
July 7, 2008 at 3:39 pm
If I understand correctly you're just replacing the double quote ('') and ! correct? The replace function can be nested like this...
update table
set , '!', ''), '''','')
where
charindex( '!', 0 and
charindex( '''', 0
If this is what I think you're looking for it's also a nice set based solution over an iterative solution.
Doug
July 7, 2008 at 3:42 pm
Darn code block! It removed the "column name" parameter
update table
set {column name}, '!', ''), '''','')
where
charindex( '!', {column name} ) > 0 and
charindex( '''', {column name} ) 0
July 7, 2008 at 3:44 pm
ok, 3rd time's a charm...
update table
set {column name} =
replace( replace( {column name}, '!', ''), '''','')
where
charindex( '!', {column name} ) > 0 and
charindex( '''', {column name} ) > 0
July 7, 2008 at 4:07 pm
SQLServerLifer (7/7/2008)
ok, 3rd time's a charm...
update table
set {column name} =
replace( replace( {column name}, '!', ''), '''','')
where
charindex( '!', {column name} ) > 0 and
charindex( '''', {column name} ) > 0
Shouldn't the above WHERE clause use an OR? As in:
UPDATE table
SET {column name} = replace(replace({column_name}, '!', ''), '''', '')
WHERE charindex('!', {column_name}) > 0
OR charindex('''', {column_name}) > 0;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 8, 2008 at 6:10 am
Thanks for the responses, but I don't think they address the problem I'm trying to solve. I don't want to update a table, I want to modify a string based on a value I retrieve from a table. So while I happen to know that I'm trying to drop a single quote and an exclamation point, at a later date I could be removing a double quote and a question mark, simply by adding it to the invalid character table. Or does this solution do that, and I'm just being especially slow this morning?
Thanks,
Mattie
July 8, 2008 at 10:57 am
I've figured it out. The table I was retrieving the value from has the InvalidCharacter field defined as char(5), because of other uses of the table. Apparently moving a char(5) value into a varchar(100) field (@StringToFind, and it's defined as way too big), and then using it in a REPLACE statement means it fails the 'does it equal to replace' test. By changing my select statement to the following, it passes the test, and does the replace.
SELECT TOP 1 @StringToFind = CAST(InvalidCharacter AS CHAR(1))
FROMOdysseyNH.dbo.SpotscInvalidCharacters
WHEREInvalidCharacterAction = 'D'
ANDInvalidCharacter > @StringToFind
ORDER BY
InvalidCharacter
This works because I'm only looking to drop a single character.
Mattie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply