36
Answers

Extracting salutation from a String in sql server

Rohan Gupta

Rohan Gupta

9y
1.2k
1
I want to Extracting Mr,Mr.,Mrs,Mrs. from a Name in sql server
Answers (36)
0
Govinda Rajulu Yemineni

Govinda Rajulu Yemineni

NA 1.5k 233.2k 9y
Hi Rohan,
try This 
 
CREATE PROCEDURE USP_Extract_Salutation
AS
BEGIN
   CREATE TABLE #table(Name varchar(100),salutation varchar(10))
   DECLARE @Name1 VARCHAR(50)
   DECLARE mycursor CURSOR
   FOR
   SELECT NAME FROM tbl_student_marks_Details
   OPEN mycursor
   FETCH NEXT FROM mycursor INTO @Name1
   WHILE @@FETCH_STATUS = 0
   BEGIN
      DECLARE @Name2 VARCHAR(50)
      SET @Name2=@Name1
 
      if LEFT(@Name2,4)='Mrs.'
         Set @Name2=Replace(@Name2,'Mrs.','');
      else if LEFT(@Name2,3)='Mrs'
         Set @Name2=Replace(@Name2,'Mrs','');
      else if LEFT(@Name2,3)='Mr.'
         SET @Name2 = Replace(@Name2,'Mr.','');
      else if LEFT(@Name2,2)='Mr'
         SET @Name2 = Replace(@Name2,'Mr','');
      DECLARE @Salutation VARCHAR(10)
      SELECT @Salutation=SUBSTRING(@Name1,1,LEN(@Name1)-LEN(@Name2))
      insert into #table values(@Name1,@Salutation)
      FETCH NEXT FROM mycursor INTO @Name1
   END
   CLOSE mycursor
   DEALLOCATE mycursor
   SELECT Name,salutation FROM #table
END
Accepted
0
Anupam Singh

Anupam Singh

NA 5.8k 886.8k 9y
It would be better to use separate column for Salutation. 
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
Yeah Govinda your right but one case not working
 
MRIDULA SINHA 
0
Govinda Rajulu Yemineni

Govinda Rajulu Yemineni

NA 1.5k 233.2k 9y
hi Rohan,
i am getting correct output for those names what you have placed
MR.V.K.KUMAR                     MR.
MR.K.C.SODHE                      MR.
Mr. Mohammed Gadhafi    Mr.
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
Thanks Govinda woking good but only one case this not working
 
MRIDULA SINHA 
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
Sorry Govinda  This not working in these names
 
MR.V.K.KUMAR
MR.K.C.SODHE
Mr. Mohammed Gadhafi 
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
sorry mr. upendra same issues here
 
AMRSH MONDAL
BHOGILAL AMRITLAL JARIWALA
SWETA KUAMR NAYAK
 
in this name  
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
sorry mr. govinda its not working in that case
 
AMRSH MONDAL
BHOGILAL AMRITLAL JARIWALA
 
SWETA KUAMR NAYAK
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y
Hi,
 
 
Use below procedure- 

CREATE PROCEDURE USP_Seperate_Salutation

AS

BEGIN

CREATE TABLE #table(Name varchar(100),salutation varchar(10))

DECLARE @Name1 VARCHAR(50)

DECLARE mycursor CURSOR

FOR

SELECT First_name FROM [dbo].[PhotograperRegistration]

OPEN mycursor

FETCH NEXT FROM mycursor INTO @Name1

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @Name2 VARCHAR(50)

SET @Name2=@Name1

Set @Name2=Replace(@Name2,'Mrs.','');

Set @Name2=Replace(@Name2,'Mrs','');

SET @Name2 = Replace(@Name2,'Mr.','');

SET @Name2 = Replace(@Name2,'Mr','');

DECLARE @Salutation VARCHAR(10)

SELECT @Salutation=SUBSTRING(@Name1,1,LEN(@Name1)-LEN(@Name2))

insert into #table values(@Name1,@Salutation)

FETCH NEXT FROM mycursor INTO @Name1

END

CLOSE mycursor

DEALLOCATE mycursor

SELECT Name,salutation FROM #table

END

 
 
Execute procedure-
 
 

USE [Photographer]

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[USP_Seperate_Salutation]

SELECT 'Return Value' = @return_value

GO

 
 
output- 
 
 
 
0
Govinda Rajulu Yemineni

Govinda Rajulu Yemineni

NA 1.5k 233.2k 9y
Hi Rohan Gupta,
try this SP
 
CREATE PROCEDURE USP_Extract_Salutation
AS
BEGIN
   CREATE TABLE #table(Name varchar(100),salutation varchar(10))
   DECLARE @Name1 VARCHAR(50)
   DECLARE mycursor CURSOR
   FOR
   SELECT NAME FROM tbl_student_marks_Details
   OPEN mycursor
   FETCH NEXT FROM mycursor INTO @Name1
   WHILE @@FETCH_STATUS = 0
   BEGIN
      DECLARE @Name2 VARCHAR(50)
      SET @Name2=@Name1
      Set @Name2=Replace(@Name2,'Mrs.','');
      Set @Name2=Replace(@Name2,'Mrs','');
      SET @Name2 = Replace(@Name2,'Mr.','');
      SET @Name2 = Replace(@Name2,'Mr','');
      DECLARE @Salutation VARCHAR(10)
      SELECT @Salutation=SUBSTRING(@Name1,1,LEN(@Name1)-LEN(@Name2))
      insert into #table values(@Name1,@Salutation)
      FETCH NEXT FROM mycursor INTO @Name1
   END
   CLOSE mycursor
   DEALLOCATE mycursor
   SELECT Name,salutation FROM #table
END 
 
Output:
 
   Name                salutation
   Mr Goving             Mr
   MrKiran                 Mr
   Mr. Gopi                Mr.
   Mr.Suresh              Mr.
   Mrs Priyanka         Mrs
   MrsLakshmi          Mrs
   Mrs. Subbu           Mrs.
   Mrs.Dolly             Mrs.
 
 
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y
Hello Rohan Gupta
 Firstly do the same as said previously then test. if not work then provide your function on same for test at my end.
Do you create function or not? 
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
hello,
  Mr. upendra this not full fill my requirement 
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
Hello,
Mr. Upendra 
 
Please tell me how to create a function 
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y
hello dear,
 
kindly create a function with parameter for this and use your created function in select query and pass  your column name in this. this may help you.
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
i want out put like this Mr. Govinda
(emp_name)                (Solutation)
Mr William Gates       Mr

Mrs  William Gates      Mrs
 
   MR.  William Gates      MR.
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
Suppose i have a table with emp_name column which have a lot of names and i extracting Salutation from names comparetivily and insert those salutation in another column
0
Govinda Rajulu Yemineni

Govinda Rajulu Yemineni

NA 1.5k 233.2k 9y
Hi Rohan Gupta,
 
   can you tell me what you suppose do after extracting salutation from name
 
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
Please tell me how to create function 
0
Govinda Rajulu Yemineni

Govinda Rajulu Yemineni

NA 1.5k 233.2k 9y
Create one Function with that accepts one input parameter i.e empname
then call that Function by passing empname as parameter
   like
      select  dbo.GetSalutationFunc(empname) from tablename
 
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y
Create a function and use my query in that function and send 2 parameter in select query.
 
like- 
 
select dbo.functionname(columnname) from tablename. 
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
Good Mr. Govinda Rajulu Yemineni but how to use column value from table .
suppose i have a table emp with empname column which has a lot of names then .. how to use this 
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
just use only 'Rohan' then gives error mr upendra kidly check bcz both type of data in table
0
Govinda Rajulu Yemineni

Govinda Rajulu Yemineni

NA 1.5k 233.2k 9y
Hi Rohan Gupta,
kindly you can try this,
   DECLARE @Name VARCHAR(50)
   SET @Name='Mr William Gates'
   DECLARE @Name1 VARCHAR(50)
   SET @Name1=@Name
   Set @Name1=Replace(@Name1,'Mrs.','');
   Set @Name1=Replace(@Name1,'Mrs','');
   SET @Name1 = Replace(@Name1,'Mr.','');
   SET @Name1 = Replace(@Name1,'Mr','');
   DECLARE @Salutation VARCHAR(10)
   SELECT @Salutation=SUBSTRING(@Name,1,LEN(@Name)-LEN(@Name1))
   SELECT @Salutation,@Name1
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y

DECLARE @c VARCHAR(50)

SET @c='mrs. Rohan'

DECLARE @Salutation VARCHAR(10)

SELECT @Salutation=SUBSTRING(@c,1,CHARINDEX(' ',@c)-0)

SET @c = Replace(Replace(@c,'Mr.',''),'','');

Set @c=Replace(@c,'Mrs.','');

Set @c=Replace(@c,'Ms.','');

SELECT @Salutation as Salutation,SUBSTRING(

@c,

CHARINDEX('_', @c) + 1,

LEN(@c) - CHARINDEX('_', @c) - CHARINDEX('_', REVERSE(@c))

)as Name

0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y
Hello ,
 
There is no error in this.
 
 

DECLARE @c VARCHAR(50)

SET @c='Mr. Rohan Gupta'

DECLARE @Salutation VARCHAR(10)

SELECT @Salutation=SUBSTRING(@c,1,CHARINDEX(' ',@c)-1)

SET @c = Replace(Replace(@c,'Mr.',''),'','');

Set @c=Replace(@c,'Mrs.','');

Set @c=Replace(@c,'Ms.','');

SELECT @Salutation as Salutation,SUBSTRING(

@c,

CHARINDEX('_', @c) + 1,

LEN(@c) - CHARINDEX('_', @c) - CHARINDEX('_', REVERSE(@c))

)as Name

0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
how please tell me... and this is your code
thrown error when we replace name 'Rohan'  place of  'Mr. U.P.Shahi'

DECLARE @c VARCHAR(50)

SET @c='Mr. U.P.Shahi'

DECLARE @Salutation VARCHAR(10)

SELECT @Salutation=SUBSTRING(@c,1,CHARINDEX(' ',@c)-1)

SET @c = Replace(Replace(@c,'Mr.',''),'','');

Set @c=Replace(@c,'Mrs.','');

Set @c=Replace(@c,'Ms.','');

SELECT @Salutation as Salutation,SUBSTRING(

@c,

CHARINDEX('_', @c) + 1,

LEN(@c) - CHARINDEX('_', @c) - CHARINDEX('_', REVERSE(@c))

)as Name

 
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y
create a function for this and then use in select query.
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
kindly tell me Mr Upendra How to check  all customer_name column value from table tbl_customer
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
DECLARE @c VARCHAR(500)
select @c = customer_name from tbl_customer
DECLARE @Salutation VARCHAR(10)
SELECT @Salutation=SUBSTRING(@c,1,CHARINDEX(' ',@c)-1)
SET @c = Replace(Replace(@c,'Mr.',''),'','');
Set @c=Replace(@c,'Mrs.','');
Set @c=Replace(@c,'Ms.','');
SELECT @Salutation as Salutation,SUBSTRING(
@c,
CHARINDEX('_', @c) + 1,
LEN(@c) - CHARINDEX('_', @c) - CHARINDEX('_', REVERSE(@c))
)as Name
 
it's thrown error like this
Msg 537, Level 16, State 2, Line 7
Invalid length parameter passed to the LEFT or SUBSTRING function.
(1 row(s) affected)
 
 
 
 
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y
 
 
Kindly use this- 
 

DECLARE @c VARCHAR(50)

SET @c='Mr. U.P.Shahi'

DECLARE @Salutation VARCHAR(10)

SELECT @Salutation=SUBSTRING(@c,1,CHARINDEX(' ',@c)-1)

SET @c = Replace(Replace(@c,'Mr.',''),'','');

Set @c=Replace(@c,'Mrs.','');

Set @c=Replace(@c,'Ms.','');

SELECT @Salutation as Salutation,SUBSTRING(

@c,

CHARINDEX('_', @c) + 1,

LEN(@c) - CHARINDEX('_', @c) - CHARINDEX('_', REVERSE(@c))

)as Name

 
 
OUTPUT- 
 
0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
Mr. @ upendra  i want to extract salutation from name and inserted salutation into another column comparetivily matching name..
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y

Hi  Rohan Gupta,

 
Use below- 
 
DECLARE @c varchar(100)

SET @c = 'MR.M.K.JANAK'

SET @c = Replace(Replace(@c,'Mr.',''),'','');

Set @c=Replace(@c,'Mrs.','');

Set @c=Replace(@c,'Ms.','');

SELECT SUBSTRING(

@c,

CHARINDEX('_', @c) + 1,

LEN(@c) - CHARINDEX('_', @c) - CHARINDEX('_', REVERSE(@c))

)

Result-


0
Rohan Gupta

Rohan Gupta

NA 442 44.7k 9y
Mr. @ govinda this is not woking when value like this " MR.M.K.JANAK"
0
Upendra Pratap Shahi

Upendra Pratap Shahi

NA 13.3k 861.5k 9y

DECLARE @c varchar(100)

SET @c = 'Mr. Upendra Pratap Shahi'

SET @c = Replace(Replace(@c,'Mr.',''),'','');

Set @c=Replace(@c,'Mrs.','');

Set @c=Replace(@c,'Ms.','');

SELECT SUBSTRING(

@c,

CHARINDEX('_', @c) + 1,

LEN(@c) - CHARINDEX('_', @c) - CHARINDEX('_', REVERSE(@c))

)

Result-

Upendra Pratap Shahi

0
Govinda Rajulu Yemineni

Govinda Rajulu Yemineni

NA 1.5k 233.2k 9y
Hi Rohan,
Try this script
 
   DECLARE @Name VARCHAR(50)
   SET @Name='Mr. William Gates'
   DECLARE @Salutation VARCHAR(10)
   SELECT @Salutation=SUBSTRING(@Name,1,CHARINDEX(' ',@Name)-1)
   SELECT @Salutation