asked on

SQl 2000 User defined function to loop through many text replace commands

I had hoped to be able to do something like the code below but if I remove all the comments it does nothing, if I leave just one line uncommented it works fine. Now I'm not a SQL coder so I am not sure whats missing!

Here's the function.

CREATE FUNCTION dbo.fnReturnCleanedtext (@Text VARCHAR(5000))
RETURNS VARCHAR(5000) AS
BEGIN
      DECLARE @Result VARCHAR(5000)
--      Set @Result = REPLACE(@Text,'"', '''');
--      Set @Result = REPLACE(@Text,'&', '');
--      Set @Result = REPLACE(@Text,'[', '');
--      Set @Result = REPLACE(@Text,']', '');
--      Set @Result = REPLACE(@Text,' O ', '');
--      Set @Result = REPLACE(@Text,' Q ', '');
--      Set @Result = REPLACE(@Text,' G ', '');
--      Set @Result = REPLACE(@Text,' R ', '')
--      Set @Result = REPLACE(@Text,' ) ', '');
      Set @Result = REPLACE(@Text,'<FP>B',' ');
--      Set @Result = REPLACE(@Text,'<FP>I',' ');
--      Set @Result = REPLACE(@Text,'<FP>S',' ');
--      Set @Result = REPLACE(@Text,'<FP>T',' ');
--      Set @Result = REPLACE(@Text,'<FP>U',' ');
--      Set @Result = REPLACE(@Text,'<FP>W',' ');
--      Set @Result = REPLACE(@Text,'<FP>-','-');
--      Set @Result = REPLACE(@Text,CHAR(13),' ');
--      Set @Result = REPLACE(@Text,CHAR(10),' ');
--      Set @Result = REPLACE(@Text,CHAR(9),' ');
--      Set @Result = LTRIM(@Text);
--      Set @Result = RTRIM(@Text);
      RETURN @Result
END

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shanedixon

ASKER

AC-

What if I wanted to store those replacement codes in a table could that UDF then read the table and loop through all the changes on each @text?

say the changes were stored in a table with field Tags. Is there a way to create the sql query and data loop in the UDF?

Anthony Perkins

I am sorry I just saw your question, but it looks like you have a solution in another thread.