SQL Nested Replace Order

Question

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

Perhaps you could make use of the esoteric parsename function like... PARSENAME(@name, 1) which will lop off everything before the . character. Example here. This will be a LOT less expensive than a million nested replaces or a UDF for that matter. — JNevill
– JNevill, Commented Oct 3, 2018 at 14:24
Thanks for suggestion... I know mine is not most efficient method, but was pressed for time. :-) — Danny Dennison
– Danny Dennison, Commented Oct 3, 2018 at 14:31
For later readers... you can introduce new problems by hacking off everything before the period when working with names as some people use a first initial instead of a full first name. If you know you will have a prefix in front of every name, then you're fine. Data sets I work with with will never let me be so lucky. Think F. Murry Abraham, or F. Scott Fitzgerald, or S. Epatha Merkerson. — Joe Mc Clure
– Joe Mc Clure, Commented Jan 16, 2024 at 14:51

Hano Johannes Rossouw · Accepted Answer · 2018-10-03 14:19:21Z

2

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.

answered Oct 3, 2018 at 14:19

Hano Johannes Rossouw

7706 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BehrouzMoslem · Accepted Answer · 2018-10-03 15:14:44Z

0

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

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', '' );

edited Oct 3, 2018 at 15:14

answered Oct 3, 2018 at 14:38

BehrouzMoslem

9,8533 gold badges29 silver badges36 bronze badges

2 Comments

Zohar Peled Over a year ago

If you replace Mr, you will not find Mr., Mrs or Mrs. in the string later on...

Danny Dennison Over a year ago

I did create a function. Again, this was something I needed to do quickly with 'easy' code I'm familiar with, but may revise using @BehrouzMoslem's "SET" methodology... assume performance would improve as well. Thanks all!

Collectives™ on Stack Overflow

SQL Nested Replace Order

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related