How to remove special characters from name excluding salutation (Mister, Ms., Mrs., Doctor )?

Question

I want to remove special characters form name excluding salutation part and Need to separate the first , last name and salutation.

Create table Employee_Name(Emp_Name varchar(20))
insert into  Employee_Name values('Dr. Sana .Singh');
insert into  Employee_Name values('Mrs. Ashley;');
insert into  Employee_Name values('Mr. Simon-');
insert into  Employee_Name values('Ms. Ale Martin galyx');
insert into  Employee_Name values('Doctor. Blank Smith&');

I need output to be

     Emp_Name            Salutation   First_Name    Last_Name
 Dr. Sana Singh              Dr.          Sana        Singh
 Mrs. Ashley                 Mrs.         Ashley    
 Mr. Simon                   Mr.          Simon 
 Ms. Ale Martin              Ms.          Ale         Martin
 Doctor. Blank Smith         Doctor.      Blank       Smith

Thanks in advance

possible duplicate of http://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values — Pranay Deep, Dec 22 '16 at 10:30

John Cappelletti · Accepted Answer · 2016-12-22T11:06:52.770

Splitting names can be a slippery slope. However, if you are looking for the first 3 strings, consider the following. Easy to expand, I'm sure you can see the pattern 1,2,3,... (I don't have much imagination)

Select A.Emp_Name
      ,Salutation = IsNull(B.Pos1,'')
      ,First_Name = IsNull(B.Pos2,'')
      ,Last_Name  = IsNull(B.Pos3,'')
 From  @Employee_Name A
 Cross Apply (
              Select Pos1 = xDim.value('/x[1]','varchar(max)')
                    ,Pos2 = xDim.value('/x[2]','varchar(max)')
                    ,Pos3 = xDim.value('/x[3]','varchar(max)')
              From (Select Cast('<x>' + Replace([dbo].[udf-Str-Strip-Non-Alphanumeric](A.Emp_Name),' ','</x><x>')+'</x>' as XML) as xDim) A
             ) B

Returns

Emp_Name                Salutation  First_Name  Last_Name
Dr. Sana .Singh         Dr          Sana        Singh
Mrs. Ashley;            Mrs         Ashley  
Mr. Simon-              Mr          Simon   
Ms. Ale Martin galyx    Ms          Ale         Martin
Doctor. Blank Smith&    Doctor      Blank       Smith

Now, stripping the the non-ALPHA characters would require a UDF

CREATE FUNCTION [dbo].[udf-Str-Strip-Non-Alphanumeric](@S varchar(max))
Returns varchar(max)
Begin
    Declare @RetVal varchar(max) = ''
    ;with  cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N) As (Select Top (IsNull(DataLength(@S),0)) Row_Number() over (Order By (Select NULL)) From cte1 a,cte1 b,cte1 c,cte1 d)
    Select @RetVal = @RetVal + Substring(@S,N,1) 
     From  cte2
     Where Substring(@S,N,1) Like '[a-z,A-Z,0-9, ]'
    Return Replace(@RetVal,'  ',' ')
End
--Select [dbo].[udf-Str-Strip-Non-Alphanumeric]('some & text. /+= a  and zip 02806')  --Returns: some  text  a  and zip 02806

@Sans Happy it helped. Just curious, what changes did you make. Perhaps it may help me as well. — John Cappelletti, Dec 22 '16 at 12:38
Actually not the few changes ..but almost changed the query , bt got the idea from yours — Sans, Dec 23 '16 at 05:52

Pranay Deep · Answer 2 · 2016-12-22T10:42:57.173

1

Try this solution, hope this helps you.

  SELECT 
  Emp_Name
 ,  Reverse(ParseName(Replace(Reverse(Emp_Name), '', ''), 1)) As [Salutation]
 , Reverse(ParseName(Replace(Reverse(Emp_Name), '', ''), 2)) As [First_Name]
 , Reverse(ParseName(Replace(Reverse(Emp_Name), '', ''), 3)) As [Last_Name]

  FROM  (Select Emp_Name from Employee_Name
  ) As [x]

Output:

Do edit if you need any improvement.

edited Dec 22 '16 at 10:42

answered Dec 22 '16 at 10:37

Pranay Deep

1,371
4
24
44

score 0 · Answer 3 · edited Jul 09 '18 at 09:27

Try as follows:

DECLARE @EMP TABLE (ID INT IDENTITY(1,1),EMP_NAME VARCHAR(100),SALUTATION VARCHAR(100),FIRST_NAME VARCHAR(100),LAST_NAME VARCHAR(100),COMMON VARCHAR(100))
DECLARE @NAME VARCHAR(100),@ID INT
INSERT INTO @EMP (EMP_NAME,SALUTATION,COMMON) SELECT EMP_NAME,SUBSTRING(EMP_NAME,0,CHARINDEX(' ',EMP_NAME)+1),REPLACE(EMP_NAME,(SUBSTRING(EMP_NAME,0,CHARINDEX(' ',EMP_NAME)+1)),'') FROM EMPLOYEE_NAME

DECLARE C CURSOR FOR
SELECT COMMON,ID FROM @EMP
OPEN C
FETCH NEXT FROM C INTO @NAME,@ID
WHILE @@FETCH_STATUS=0
BEGIN
IF(CHARINDEX(' ',@NAME))>0
BEGIN
UPDATE @EMP SET FIRST_NAME=(SELECT SUBSTRING(@NAME,0,CHARINDEX(' ',@NAME)+1)) WHERE ID=@ID
UPDATE @EMP SET LAST_NAME=(REPLACE(@NAME,(SUBSTRING(@NAME,0,CHARINDEX(' ',@NAME)+1)),'')) WHERE ID=@ID
IF(CHARINDEX(' ',(SELECT LAST_NAME FROM @EMP WHERE ID=@ID)))>0
BEGIN
    UPDATE @EMP SET LAST_NAME=(SELECT SUBSTRING(LAST_NAME,0,CHARINDEX(' ',LAST_NAME)+1) FROM @EMP WHERE ID=@ID) WHERE ID=@ID    
    END
END
ELSE
BEGIN
UPDATE @EMP SET FIRST_NAME=@NAME WHERE ID=@ID
END
FETCH NEXT FROM C INTO @NAME,@ID
END
CLOSE C
DEALLOCATE C
SELECT EMP_NAME,SALUTATION,FIRST_NAME,LAST_NAME FROM @EMP

How to remove special characters from name excluding salutation (Mister, Ms., Mrs., Doctor )?

3 Answers3