Question about replace function

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 2005 Forums

Transact-SQL (2005)

Question about replace function

Author

Topic

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2008-10-15 : 14:14:20

Hello,

Dumb question for you. I need to replace a large number (roughly 130) characters in a field returned from a query. Most of them are ascii characters that I want to replace with the numerical code so it will properly display in an html doc. If I don't replace it, due to another application, it gets goofed up.

What's the best way to do something like this? I'd rather not nest 130 replace calls. Would a function with a bunch of "set @string=replace(@string, '~','#126;')" lines work ok? Or is there something better?

Thanks for any help, I really appreciate it.

Nick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-15 : 14:17:09

How often does it need to run?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2008-10-15 : 14:26:29

quote:
Originally posted by tkizer

How often does it need to run?

The query runs fairly frequently (up to hundreds of times per hour depending on the day). The app that is being run needs to be modified to reduce the number of database calls, but I'm not sure when that will happen.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 14:27:17

Is this for PHP, .NET , CF or what. i'd take this ascii coding to the built-in encoding functions.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-15 : 14:29:08

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ReplaceWebChars]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ReplaceWebChars]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE function ReplaceWebChars (@dat varchar(8000))
returns varchar(8000)
as
begin
declare @BadChr varchar(100), @x int, @y int
set @BadChr = ':,#$%@~'
select @x=1,@y=len(@BadChr)
while @x <= @y
begin
set @dat = replace(@dat, substring(@BadChr,@x,1), '#'+Cast(ascii(substring(@BadChr,@x,1))as varchar)+';')
set @x=@x+1
end

return @dat
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

select dbo.ReplaceWebChars('Hello ~World');

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-15 : 14:45:38

quote:
Originally posted by nickfinity

quote:
Originally posted by tkizer

How often does it need to run?

The query runs fairly frequently (up to hundreds of times per hour depending on the day). The app that is being run needs to be modified to reduce the number of database calls, but I'm not sure when that will happen.

Can't you modify the source code so that proper characters are placed in the database in the first place?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2008-10-15 : 14:47:39

quote:
Originally posted by hanbingl

Is this for PHP, .NET , CF or what. i'd take this ascii coding to the built-in encoding functions.

It's an application that I don't have the source for. So I need to find a way to get the numeric code out from the database. I wish I could do it in a programming environment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources