Replace characters in a string
-
I need to replace certain characters in a string before inserting into a table. I would like a function to do this and that I can use to specify multiple characters. For example, % & *. I know about the replace function but is this the best way to do this? Does a UDF provide any advantages over a multiple replace? Thanks.
-
The only advantage is logic encapsulation. Other than that, any tsql code will have to replace the same string many times.
-
Does anyone have an example of a UDF that I could use?
-
I would search this site. You will find numerous answers just in the Discussion section.
Here is one possible solution: <A HREF="http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=18554" target = "new">Possible Solution</A>
Remi also has a function out there which uses his numbers table (a useful table to have in your database...).
Good hunting.
I wasn't born stupid - I had to study.
-
I remember posting a great solution for this problem but I didn't save it. It was one of our discussion about procedural/set solutions but I just can't find it. Can you try to find it?
-
I think this is it. [Now, tell me what I did wrong in posting the link
]. IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Numbers]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Numbers]
GO
CREATE TABLE [dbo].[Numbers]( [PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED( [PkNumber]) ON [PRIMARY] ) ON [PRIMARY]
GO
-----------------------------------------------------------------------------------------
DECLARE @i as int
SET @i = 0
WHILE @i < 8000
BEGIN
INSERT INTO dbo.Numbers DEFAULT VALUES
SET @i = @i + 1
END
GO
-----------------------------------------------------------------------------------------
-- Remi Gregoire function
CREATE FUNCTION dbo.RemoveChars (@Input as varchar(8000))
RETURNS VARCHAR(8000)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Return AS varchar(8000)
SET @Return = ''
SELECT @Return = @Return + SUBSTRING( @Input, PkNumber, 1)
FROM dbo.Numbers
WHERE ASCII( SUBSTRING( @Input, PkNumber, 1)) BETWEEN 48 AND 57
AND PkNumber <= LEN( @Input)
RETURN @Return
END
GO
-----------------------------------------------------------------------------------------
SELECT dbo.RemoveChars('l3l45kjhsf87y3')
GO
SELECT dbo.RemoveChars('123 2nd Street')
GO
-----------------------------------------------------------------------------------------
DROP Function RemoveChars
GO
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Numbers]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Numbers]
GO
I wasn't born stupid - I had to study.
-
Thanks for the reply. However, can someone give me a brief explanation as to what this UDF does? Will it replace chraracters such as #, &, %, etc? Its not just numbers that I am worried about, its the special characters that could present problems when returning results to a web application.
-
I'll be able to answer that one once Farrell finds the right post.
Farrell I meant the one where there was a search/replace table and where the udf search/replaced all the characters in one pass. I had a few guys tell me that it didn't work the way I coded it... untill they tried it and found otherwise
. -
Sorry.
I actually use one like that at work. Once I get into the office I will look for it... (Remi, it may not be the exact one you coded, but it might spur your memory).
I still want to know what I did wrong in my hyper-link code above...
I wasn't born stupid - I had to study.
-
The thread with Remi's 'magic' function is called "stripping out quotes"
A possible drawback with the solution may be that it relies on a separate table defining 'bad char' => 'good char' pairs, which may become difficult to manage if one get's carried away.
Another possible drawback may be that everything defined in that 'control-table' will always be applied whenever the function is used. Perhaps somewhat undesirable for something as generic as a function. Should you need other rules or replacements for certain events, you can't have them as is.
Other than that, it seems like a slick non-procedural approach
btw, dunno about the link.. don't know how to do them (which is why the thread isn't linked)
/Kenneth
-
"I still want to know what I did wrong in my hyper-link code above... "
You posted it using IE with a text editor, it creates the link automatically for you without having you writting the html tags.
-
To make the hyperlink work properly in your posts make sure you click the html button at the bottom so you aren't in the wysiwyg environment. You can then code the hyperlink the way you want it, like this: TEST LINK
-
And here's a repost of the solution.
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.ReplaceSpecialCharacter2000') and XType = 'FN')
DROP FUNCTION dbo.ReplaceSpecialCharacter2000
GO
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.SpecialCharacters') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.SpecialCharacters
GO
CREATE TABLE dbo.SpecialCharacters(
Search char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL primary key clustered,
Replacement varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Explanation varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Insert into dbo.SpecialCharacters (Search, Replacement, Explanation) values ('''', '', 'Test')
Insert into dbo.SpecialCharacters (Search, Replacement, Explanation) values ('"', '', 'Test')
Insert into dbo.SpecialCharacters (Search, Replacement, Explanation) values (' ', '_', 'Test')
Insert into dbo.SpecialCharacters (Search, Replacement, Explanation) values ('@', 'A', 'Test')
Insert into dbo.SpecialCharacters (Search, Replacement, Explanation) values ('\', '\\', 'Test')
GO
CREATE FUNCTION dbo.ReplaceSpecialCharacter2000 ( @ValueToAlter AS varchar(2000))
RETURNS varchar(2000)
AS
BEGIN
Select @ValueToAlter = Replace (@ValueToAlter, Search, Replacement) from dbo.SpecialCharacters
RETURN ( @ValueToAlter )
END
GO
Select dbo.ReplaceSpecialCharacter2000 ('f''gr"7 df@@8j\') AS Replaced
Go
--fgr7_dfAA8j\
GO
DROP FUNCTION ReplaceSpecialCharacter2000
DROP TABLE SpecialCharacters
Now the SpecialCharacters table could be modified to have a type search where not all of the s/r would be done at the same time (if need be).
-
>>Now the SpecialCharacters table could be modified to have a type search where not all of the s/r would be done at the same time (if need be).
That is one way to build more flexibility. Though it may also make it even more difficult to maintain/debug if the # rows in the table starts to grow..
It all depends
(probably a non-issue for most applications anyway)
/Kenneth
-
That's it, Remi! Glad I could help....
Actually, we have found the flexibility of using this table extremely helpful - especially for odd characters pasted into ASP pages used for ad hoc reports... Ours hasn't ended up being that large and it moves pretty quickly through the UDF.
It seems tedious, but I "highly" recommend you fill in the Explanation field as six months from now you won't remember why that character was added....
I wasn't born stupid - I had to study.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply