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
shanedixonAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
CREATE FUNCTION dbo.fnReturnCleanedtext (@Text VARCHAR(5000))
RETURNS VARCHAR(5000) AS
BEGIN
      DECLARE @Result VARCHAR(5000)
      Set @Result = REPLACE(@Text,'"', '''');
      Set @Result = REPLACE(@Result,'&', '');
      Set @Result = REPLACE(@Result,'[', '');
      Set @Result = REPLACE(@Result,']', '');
      Set @Result = REPLACE(@Result,' O ', '');
      Set @Result = REPLACE(@Result,' Q ', '');
      Set @Result = REPLACE(@Result,' G ', '');
      Set @Result = REPLACE(@Result,' R ', '')
      Set @Result = REPLACE(@Result,' ) ', '');
      Set @Result = REPLACE(@Result,'<FP>B',' ');
      Set @Result = REPLACE(@Result,'<FP>I',' ');
      Set @Result = REPLACE(@Result,'<FP>S',' ');
      Set @Result = REPLACE(@Result,'<FP>T',' ');
      Set @Result = REPLACE(@Result,'<FP>U',' ');
      Set @Result = REPLACE(@Result,'<FP>W',' ');
      Set @Result = REPLACE(@Result,'<FP>-','-');
      Set @Result = REPLACE(@Result,CHAR(13),' ');
      Set @Result = REPLACE(@Result,CHAR(10),' ');
      Set @Result = REPLACE(@Result,CHAR(9),' ');
      Set @Result = LTRIM(@Result);
      Set @Result = RTRIM(@Result);
      RETURN @Result
END
0
 
Anthony PerkinsConnect With a Mentor Commented:
Or simply:

CREATE FUNCTION dbo.fnReturnCleanedtext (@Text VARCHAR(5000))
RETURNS VARCHAR(5000) AS
BEGIN
      Set @Text = REPLACE(@Text,'"', '''');
      Set @Text = REPLACE(@Text,'&', '');
      Set @Text = REPLACE(@Text,'[', '');
      Set @Text = REPLACE(@Text,']', '');
      Set @Text = REPLACE(@Text,' O ', '');
      Set @Text = REPLACE(@Text,' Q ', '');
      Set @Text = REPLACE(@Text,' G ', '');
      Set @Text = REPLACE(@Text,' R ', '')
      Set @Text = REPLACE(@Text,' ) ', '');
      Set @Text = REPLACE(@Text,'<FP>B',' ');
      Set @Text = REPLACE(@Text,'<FP>I',' ');
      Set @Text = REPLACE(@Text,'<FP>S',' ');
      Set @Text = REPLACE(@Text,'<FP>T',' ');
      Set @Text = REPLACE(@Text,'<FP>U',' ');
      Set @Text = REPLACE(@Text,'<FP>W',' ');
      Set @Text = REPLACE(@Text,'<FP>-','-');
      Set @Text = REPLACE(@Text,CHAR(13),' ');
      Set @Text = REPLACE(@Text,CHAR(10),' ');
      Set @Text = REPLACE(@Text,CHAR(9),' ');
      Set @Text = LTRIM(@Text);
      Set @Text = RTRIM(@Text);
      RETURN @Text
END
0
 
shanedixonAuthor Commented:
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?
0
 
Anthony PerkinsCommented:
I am sorry I just saw your question, but it looks like you have a solution in another thread.
0
All Courses

From novice to tech pro — start learning today.