to replace more than one expression

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

SQL Server Development (2000)

to replace more than one expression

Author

Topic

rajesha
Starting Member

36 Posts

Posted - 2002-06-06 : 01:22:52

consider there is a string variable

a="olk]g=nm!"
i want to replace all the non character's{here ],=,!) by blank " "

by replace function is it possible.or any other function is avalable

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-06-06 : 02:58:33

As far as I know there is no buit-in function for this. But you can make your own function. like this
-------------------------
set nocount on
declare @x varchar(100), @i int, @t varchar(100),@c char(1)
set @x = 'olk]g=nm!'
select @t= @x
while len(@t) <> 0
begin
select @c = left(@t,1)
if not ((ascii(@c) between 65 and 90) or (ascii(@c) between 97 and 122))
begin
select @x = replace(@x,@c,'')
end
select @t = right(@t,len(@t) -1)
end
select @x
-------------------------

Ramesh Singh

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-06 : 03:12:53

Rejesha,

Here is UDF for SQL 2K


CREATE FUNCTION ufStripNonChar
(@String VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
WHILE PATINDEX('%[^a-zA-Z0-9]%',@String) != 0
 BEGIN
SELECT @String = STUFF(@String, PATINDEX('%[^a-zA-Z0-9]%',@String),1,'')
 END
RETURN (@String)
END
go
SELECT dbo.ufStripNonChar('ol&^%k&g=nm!')

You can change the size of the Parameter size from 50 to whatever....

HTH

DavidM

"SQL-3 is an abomination.."

rajesha
Starting Member

36 Posts

Posted - 2002-06-06 : 07:33:28

i have written this stored procedure in database stored procedure.
i want to call this stored procedure through query analyser how can i do that?

Nazim
A custom title

1408 Posts

Posted - 2002-06-06 : 08:36:13

a simple
execute storedprocedure parameterlist

will do. i would suggest you to spend more time in reading Booksonline.

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

rajesha
Starting Member

36 Posts

Posted - 2002-06-07 : 09:34:26

if my string is
a='jks'ldf'( one single quote in the string)
select patindex('%[^a-zA-Z0-9]%','jks'ldf')
it is showing an error..
how to resolve so that if there is a single quote also there in the string..i will get a value for the above function

except '(single quote) all others are working

Nazim
A custom title

1408 Posts

Posted - 2002-06-08 : 00:23:19

First replace all single quotes in your string with two single quotes. otherwise on every point you will face problems handling such strings.

Check this Article http://www.sqlteam.com/item.asp?ItemID=293 .

HTH

---------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.--
Ralph Waldo Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources