replace a small list of characters in query

Shawn used Ask the Experts™
this query is getting out of hand and I'm not done. is there any way instead of nesting replace() that I can just have a list?

look for list "?,/,|" and replace it with "-"
SELECT     PageURL, FriendlyPageFrench,  
LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(PageTitleFrench)), '[/|]', '-'), ' | ', '-'), ' ', '-'), '''', ''), '?', ''), '--', '-'), 
                      '--', '-')) AS newfpagefrench, PageTitleFrench
FROM         tblSiteMetaData
WHERE     (FriendlyPageFrench IS NULL) AND (NOT (PageTitleFrench IS NULL)) AND (NOT (PageTitleFrench = N'submitform')) AND (NOT (PageTitleFrench = N'download')) AND 
                      (NOT (PageTitleFrench = N'not sure yet')) AND (NOT (PageTitleFrench = N'to delete soon. need to fix bug first'))
ORDER BY PageTitleFrench

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nathan RileyFounder

I don't believe so
I recommend that you put your replace logic into a function, then use that function in your query that way your query is clean and the replacement code is re-usable.


good recommendation but it doesn't really answer the question.

I'm sure there's a way to do this.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Think the question has been answered.  No there is NO way to do this using REPLACE as it will not allow you to pass a list of characters unless they are contiguous.  For example, if you were replacing the word "abc" that would work, but it will not replace all "a", "b" and "c" occurrences in that manner.

As a work around, you would write your own user defined function that took in the varchar field and a list of characters which you use in your function to clean/strip characters from the varchar field which you return for display without the listed characters.


Top Expert 2012

>>is there any way instead of nesting replace() that I can just have a list?<<
Yes, there is a way, by placing the values in a table and then joining against this table.  Is it more efficient?  Probably not.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

acperkins, that was my thought with using the user defined function by taking the list of characters and splitting into a table variable and then doing the replace from the table variable.
Top Expert 2012

I guess the author did not like the idea.  Oh well.


sorry, I didn't understand what you meant by a function...fairly new to sql server.

would you be able to help me start it. now sure how to do this

Hi ,

You are very lucky i have this function takes a string , a list from chars for replace , and a list to chars

you can call it in you select like
dbo.ReplCharsList(PageTitleFrench, '/-<>+' , '-----')

but be aware the '/-<>+' and  '-----' must be equal cause this function do a replace char by array position 1 is replaced with position 1

See the code below...
Hope this help you
Create Function dbo.ReplCharsList ( @FromString VarChar(8000), @FromCharsList VarChar(8000) , @ToCharsList VarChar(8000) ) Returns VarChar(8000) AS

        Declare @IncorrectCharLoc As SmallInt
        Declare @FromChar As Char(1)
        Declare @ToChar As Char(1)

        /* Return String As Is If @FromCharsList <> @ToCharsList */
        If ( LEN(@FromCharsList) <> LEN(@ToCharsList) ) Return @FromString

        While ( LEN(@FromCharsList) > 0 )

                        /* Take One Char */
                        Set @FromChar = SubString(@FromCharsList , 1 , 1 )
                        /* Take The Replacement */
                        Set @ToChar = SubString(@ToCharsList , 1 , 1 )

                        /* Make The Replacement */
                        Set @FromString = Replace(@FromString,@FromChar,@ToChar)

                        /* Fix The Rest String For While Loop */
                        Set @FromCharsList = SubString(@FromCharsList , 2,(Len(@FromCharsList) - 1 ))
                        Set @ToCharsList = SubString(@ToCharsList , 2,(Len(@ToCharsList) - 1 ))
        /* Last Retrun The New String */
        Return @FromString

Open in new window

Top Expert 2012
I tried to post this earlier without any luck.  Here it goes again:
1. Build  a table:
          CREATE TABLE Replacements(
                    String1 varchar(10),          -- Change data types as appropriate
                    String2 varchar(10)

2. Add an index:
          CREATE UNIQUE CLUSTERED INDEX IX_Replacements ON Replacements(String1)

3. Add all the values you need to replace:
          INSERT Replacements(String1, String2) VALUES('[/|]', '-')
          INSERT Replacements(String1, String2) VALUES(' | ', '-'))
          INSERT Replacements(String1, String2) VALUES(' ', '-')
          INSERT Replacements(String1, String2) VALUES('''', '')
          INSERT Replacements(String1, String2) VALUES('?', '')
          INSERT Replacements(String1, String2) VALUES('--', '-')

4. Create a function:
      CREATE FUNCTION dbo.udf_MyReplacement (
                  @Value varchar(100)          -- Change data type as appropriate
      RETURNS varchar(100)                      -- Change data type as appropriate



      SET @Value = LTRIM(RTRIM(@Value))

      SELECT      @Value = REPLACE(@Value, String1, String2)
      FROM      Replacements

      RETURN LOWER(@Value)


5. Re-write your select statement:
      SELECT  PageURL,
              dbo.udf_MyReplacement(PageTitleFrench) AS newfpagefrench,
      FROM    tblSiteMetaData
      WHERE   FriendlyPageFrench IS NULL
              AND PageTitleFrench NOT IN (N'submitform', N'download', N'not sure yet', N'to delete soon. need to fix bug first')
      ORDER BY PageTitleFrench
Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Exactly, and if you had a table OR a split function that returns a table variable you could have something like this in your function:

DECLARE @cleanedPhoneNo NVARCHAR(50);
SET @cleanedPhoneNo = '+1 555 555 5555 EXT 1234'

SELECT @cleanedPhoneNo = REPLACE(@cleanedPhoneNo, value, '')
FROM dbo.split('+,(,),[,],A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z', ',') c

SELECT @cleanedPhoneNo

Where value is the column holding the individual characters of the split.  This should demonstrate what AC meant by using a table I believe.  He can correct me if I am wrong.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

*laughing* didn't see your post, Anthony, when I started typing.  Guess I did understand you correctly on what you meant.  :) Definitely works.  As you said, may not be most efficient, but this is a cleansing job and those seldom are based on efficiency but on getting the data cleaned any way possible.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial