CODEWITHSUNDEEP
sqlt-sqlreplacenested
Danny Dennison
Danny Dennison

Reputation: 7

SQL Nested Replace Order

Dumb question, but can't seem to find a quick answer... I am creating UDF to replace name prefix with a nested replace... something like this:

REPLACE(
 REPLACE(
  REPLACE(
   REPLACE(
    REPLACE(
     REPLACE(
      REPLACE(
       REPLACE(
        @Name
       , 'Mr ', '')
      , 'Mrs ', '')
     , 'Ms ', '')
    , 'Dr ', '')
   , 'Mr. ', '')
  , 'Mrs. ', '')
 , 'Ms. ', '')
, 'Dr. ', '')

but want to replace those containing a '.' BEFORE those without. What's the order/hierarchy of nested replace? TIA, Danny

Upvotes: 0

Views: 2894

Answers (2)

BehrouzMoslem
BehrouzMoslem

Reputation: 9713

I suggest using a function like this in SQL:

CREATE FUNCTION [dbo].[ReplaceNamePrefix]
(@value NVARCHAR(50)
)
RETURNS NVARCHAR(50)
AS
     BEGIN
         DECLARE @RETURN NVARCHAR(50);
         SET @RETURN = @value;
         SET @RETURN = REPLACE(@RETURN, 'Mrs.', ''); 
         SET @RETURN = REPLACE(@RETURN, 'Mrs', '');
         SET @RETURN = REPLACE(@RETURN, 'Mr.', '');
         SET @RETURN = REPLACE(@RETURN, 'Mr', '');
         SET @RETURN = REPLACE(@RETURN, 'Ms.', '');
         SET @RETURN = REPLACE(@RETURN, 'Ms', ''); 
         SET @RETURN = REPLACE(@RETURN, 'Dr. ', '');
         SET @RETURN = REPLACE(@RETURN, 'Dr', ''); ');
         SET @RETURN = RTRIM(LTRIM(@RETURN));
         RETURN @RETURN;
     END;

Use the following command to get your goal: 

SELECT [dbo].[ReplaceNamePrefix]('Dr. Danny Dennison');

the result is: Danny Dennison

enter image description here

But if you insist on using your own code, use the following:

SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 'MR  Danny Dennison', 'Mrs.', '' ), 'Mrs', '' ), 'Mr.', '' ), 'Mr', '' ), 'Ms.', '' ), 'Ms', '' ), 'Dr. ', '' ), 'Dr', '' );

Upvotes: 0

Hano Johannes Rossouw
Hano Johannes Rossouw

Reputation: 770

Your inner most Replace will always run first on the character string. So from inner to outer is the order in which they will be executed. I suggest though trying a more elegant approach perhaps through the use of regex and case statements so it might be easier to maintain rather than just having a ton of nested replaces which don't necessarily all do work.

Upvotes: 2

Related Questions