UDF using the REPLACE function

Question

UDF using the REPLACE function

Farrell Keough

SSCoach

Points: 17414
More actions
February 25, 2005 at 12:22 pm

#60989

I have been tasked with writing a UDF to replace various characters which may exist in @Variables within Stored Procedures. For instance, it may be a string with a CHAR(13) [carriage return] or other odd characters.
I can write a long REPLACE string, but I was hoping for something a little more elegant; I tend to be a combat boots in a powder blue tux elegance myself, (or maybe better put, a bulldozer).
I tried a search on the site, but found little to nothing. If anyone has some snippet or good direction to lead me, the help would be appreciated.

Thanks in advance.
I wasn't born stupid - I had to study.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I think I have it. I am going to create a table named something like "SpecialCharacters". It will have two fields, one for the character and a second for a description. That way, the UDF can loop through that table and replace what goofy characters get inserted that will muck up our code.

Let me know if you see any problems with this approach. Thanks

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Always depends on how you "loop" but I think you can figure out a fast way of doing that.

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I appreciate your confidence in me, but I think it may be misplaced.

Could you offer an idea of how to make that faster? For instance, it strikes me that checking if the character exists in the @variable would take just as much time as running the REPLACE function itself even if the character is not there...

(Actually, I am now using three fields, one for the special character, one for the replacement value, and a third as a description).

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 4

if you follow the table route probably generating the sql dynamically is the fastest way

HTH

* Noel

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

I don't think I need to; albiet I now am adding another ID field to the table.

DECLARE @Variable varchar(1),

@TestScript varchar(25)

SELECT @Variable = 'I'

SELECT @TestScript = 'I LEFT MY HEART'

SELECT @TestScript

SELECT @TestScript = REPLACE( @TestScript, @Variable, 'YOU')

SELECT @TestScript

All I would need to do it keep updating @Variable to the next record in the table containing SpecialCharacters. This would prevent generating new SPIDs every time a replacement is made and that should keep the IO down and speed up...

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 6

suppose you want to run your replace on a TABLE.

Are you going to run your loop on every record ?

wouldn't it be better and SET BASED to create the replace( replace ( ...

pattern and run it ONCE ?

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I just played with it... have a go with this script and post the final result to us :

if object_id ('Replaces') > 0

begin

drop table Replaces

end

GO

if object_id ('Dummys') > 0

begin

drop table Dummys

end

GO

create table Replaces(

Search varchar(50) not null, Replasse varchar(50) not null)

GO

CREATE TABLE Dummys (Test varchar(50))

GO

Insert into Replaces (Search, Replasse) values ('I', 'YOU')

Insert into Replaces (Search, Replasse) values ('LEFT', 'LEFTED')

Insert into Replaces (Search, Replasse) values ('HEART', 'LIVER')

GO

Insert into Dummys (Test) values ('I LEFT MY HEART' )

GO

Declare @SearchString as varchar(100)

Declare @FieldName as varchar(50)

Declare @TableName as varchar(50)

set @FieldName = 'Test'

set @TableName = 'Dummys'

set @SearchString = ''

Select @SearchString = 'REPLACE(' + @SearchString + '|!"/' + ', ''' + Search + ''', ''' + Replasse + ''')' FROM Replaces

SET @SearchString = REPLACE(LEFT(@SearchString, CHARINDEX('|!"/', @SearchString, 1) - 1) + @FieldName + RIGHT(@SearchString, LEN(@SearchString) - (CHARINDEX('|!"/', @SearchString, 1) + 3)), '|!"/', '')

Select 'UPDATE ' + @TableName + ' SET ' + @FieldName + ' = ' + @SearchString

--executing previous results

Select * from Dummys

UPDATE Dummys SET Test = REPLACE(REPLACE(REPLACE(Test, 'I', 'YOU'), 'LEFT', 'LEFTED'), 'HEART', 'LIVER')

Select * from Dummys

/*

'I LEFT MY HEART'

becomes

'YOU LEFTED MY LIVER'

*/

DROP TABLE Replaces

DROP TABLE Dummys

Farrell Keough SSCoach Points: 17414 More actions · Answer 8

Oh this is infuriating - I lost everything I just wrote!!

First, this will never be used on a table, hence that is not an issue. I should have been more specific. This is for an existing application in which odd characters can be introduced and the need to replace them exists (I did not write it and can not change it). I would like this UDF to be flexible though, as other applications here may have similar circumstance.

I do not regret, (other than taking your time Remi) the response I got!! That was the funniest reply I have seen in ages!!! "I lefted my liver, in San Fransisco!" That was the best! What a great way to start the weekend... Thanks!

Basically, I wanted a better approach than 60 gazillion REPLACE( @Variable, 'X', 'Y') statements surrounding a passed in @Varaible.

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Happy that you found that funny... and I just made myself a nice base for a future function.

But I'm sorry to tell you that I know no other work around than to build multiple replaces around one another.

Farrell Keough SSCoach Points: 17414 More actions · Answer 10

I built a function to handle this with Invoice Numbers for an accounting contact I had, so I fully understand.

But, I think in this case, one REPLACE looping through the values I have determined will be a problem should suffice. Once I have written this, I will post it so we can all test and try to break it to make it sure it is worth rolling out...

I wasn't born stupid - I had to study.

Farrell Keough SSCoach Points: 17414 More actions · Answer 11

It took me a bit since I had other stuff to attend to in the mean time.

This is what I have developed so far. It is needed because the ASP page insert errant characters, (I cannot do anything about that at this point). Since this uses Dynamic SQL to generate reports, having these characters really goofs things up. Rather than write a bunch of REPLACE statements, we are going to try this approach.

The only thing I don't like is you must write dbo.ReplaceSpecialCharacter2000(@Variable) rather than just ReplaceSpecialCharacter2000(@Varaible). Oil well...

Let me know what you think.

----------------------------------------------------------------------------------

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(

RowID integer IDENTITY (1, 1) NOT NULL,

SearchForCharacter varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

ReplacementCharacter varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

Explanation varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

GO

CREATE UNIQUE CLUSTERED INDEX [cuidx_SearchForCharacter] ON dbo.SpecialCharacters( SearchForCharacter) ON [PRIMARY]

GO

CREATE INDEX [idx_RowID] ON dbo.SpecialCharacters( RowID) ON [PRIMARY]

GO

----------------------------------------------------------------------------------