replace a small list of characters in query

Shawn
Shawn used Ask the Experts™
on
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

Comment
Watch Question

Do more with

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

Commented:
I don't believe so
Commented:
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.

Author

Commented:
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

Commented:
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.

Regards,

--isa
Top Expert 2012

Commented:
>>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

Commented:
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

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

Author

Commented:
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

Commented:
Hi ,

You are very lucky i have this function ready....it 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 char...like 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
Begin

        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 )
                Begin

                        /* 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 ))
                End
        /* Last Retrun The New String */
        Return @FromString
End
GO

Open in new window

Top Expert 2012
Commented:
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

      AS

      BEGIN

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

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

      RETURN LOWER(@Value)

      END

5. Re-write your select statement:
      SELECT  PageURL,
              FriendlyPageFrench,
              dbo.udf_MyReplacement(PageTitleFrench) AS newfpagefrench,
              PageTitleFrench
      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
Commented:
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

Commented:
*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