Extracting salutation from a String in sql server

Question

I want to Extracting MrMr.MrsMrs. from a Name in sql server

Govinda Rajulu Yemineni · Answer

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(@Name24)='Mrs.' Set @Name2=Replace(@Name2'Mrs.'''); else if LEFT(@Name23)='Mrs' Set @Name2=Replace(@Name2'Mrs'''); else if LEFT(@Name23)='Mr.' SET @Name2 = Replace(@Name2'Mr.'''); else if LEFT(@Name22)='Mr' SET @Name2 = Replace(@Name2'Mr'''); DECLARE @Salutation VARCHAR(10) SELECT @Salutation=SUBSTRING(@Name11LEN(@Name1)-LEN(@Name2)) insert into #table values(@Name1@Salutation) FETCH NEXT FROM mycursor INTO @Name1 END CLOSE mycursor DEALLOCATE mycursor SELECT Namesalutation FROM #table END

Anupam Singh · Answer

It would be better to use separate column for Salutation.

Rohan Gupta · Answer

Yeah Govinda your right but one case not working MRIDULA SINHA

Govinda Rajulu Yemineni · Answer

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.

Rohan Gupta · Answer

Thanks Govinda woking good but only one case this not working MRIDULA SINHA

Rohan Gupta · Answer

Sorry Govinda This not working in these names MR.V.K.KUMAR MR.K.C.SODHE Mr. Mohammed Gadhafi

Rohan Gupta · Answer

sorry mr. upendra same issues here AMRSH MONDAL BHOGILAL AMRITLAL JARIWALA SWETA KUAMR NAYAK in this name

Rohan Gupta · Answer

sorry mr. govinda its not working in that case AMRSH MONDAL BHOGILAL AMRITLAL JARIWALA SWETA KUAMR NAYAK

Upendra Pratap Shahi · Answer

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-

Govinda Rajulu Yemineni · Answer

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(@Name11LEN(@Name1)-LEN(@Name2)) insert into #table values(@Name1@Salutation) FETCH NEXT FROM mycursor INTO @Name1 END CLOSE mycursor DEALLOCATE mycursor SELECT Namesalutation 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.

Upendra Pratap Shahi · Answer

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?

Rohan Gupta · Answer

hello Mr. upendra this not full fill my requirement

Upendra Pratap Shahi · Answer

Hello http://www.codeproject.com/Articles/167399/Using-Table-Valued-Functions-in-SQL-Server

Rohan Gupta · Answer

Hello Mr. Upendra Please tell me how to create a function

Upendra Pratap Shahi · Answer

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.

Rohan Gupta · Answer

i want out put like this Mr. Govinda (emp_name) (Solutation) Mr William Gates Mr Mrs William Gates Mrs MR. William Gates MR.

Rohan Gupta · Answer

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

Govinda Rajulu Yemineni · Answer

Hi Rohan Gupta can you tell me what you suppose do after extracting salutation from name

Rohan Gupta · Answer

Please tell me how to create function

Govinda Rajulu Yemineni · Answer

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

Upendra Pratap Shahi · Answer

Create a function and use my query in that function and send 2 parameter in select query. like- select dbo.functionname(columnname) from tablename.

Rohan Gupta · Answer

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

Rohan Gupta · Answer

just use only 'Rohan' then gives error mr upendra kidly check bcz both type of data in table

Govinda Rajulu Yemineni · Answer

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(@Name1LEN(@Name)-LEN(@Name1)) SELECT @Salutation@Name1

Upendra Pratap Shahi · Answer

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

Upendra Pratap Shahi · Answer

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

Rohan Gupta · Answer

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

Upendra Pratap Shahi · Answer

create a function for this and then use in select query.

Rohan Gupta · Answer

kindly tell me Mr Upendra How to check all customer_name column value from table tbl_customer

Rohan Gupta · Answer

DECLARE @c VARCHAR(500) select @c = customer_name from tbl_customer DECLARE @Salutation VARCHAR(10) SELECT @Salutation=SUBSTRING(@c1CHARINDEX(' '@c)-1) SET @c = Replace(Replace(@c'Mr.''')''''); Set @c=Replace(@c'Mrs.'''); Set @c=Replace(@c'Ms.'''); SELECT @Salutation as SalutationSUBSTRING( @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)

Upendra Pratap Shahi · Answer

Hello Rohan Gupta  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-

Rohan Gupta · Answer

Mr. @ upendra i want to extract salutation from name and inserted salutation into another column comparetivily matching name..

Upendra Pratap Shahi · Answer

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-

Rohan Gupta · Answer

Mr. @ govinda this is not woking when value like this  MR.M.K.JANAK

Upendra Pratap Shahi · Answer

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

Govinda Rajulu Yemineni · Answer

Hi Rohan Try this script DECLARE @Name VARCHAR(50) SET @Name='Mr. William Gates' DECLARE @Salutation VARCHAR(10) SELECT @Salutation=SUBSTRING(@Name1CHARINDEX(' '@Name)-1) SELECT @Salutation