Extracting salutation from a String in sql server

Yeah Govinda your right but one case not working

 

MRIDULA SINHA 

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.

Thanks Govinda woking good but only one case this not working

 

MRIDULA SINHA 

Sorry Govinda  This not working in these names

 

MR.V.K.KUMAR

MR.K.C.SODHE

Mr. Mohammed Gadhafi 

sorry mr. upendra same issues here

 

 

in this name  

sorry mr. govinda its not working in that case

 

AMRSH MONDAL

BHOGILAL AMRITLAL JARIWALA

 

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- 

 

 

 

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:

 

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? 

hello,

  Mr. upendra this not full fill my requirement 

Hello,

 

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

Hello,

Mr. Upendra 

 

Please tell me how to create a function 

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.

 

Hi Rohan Gupta,

 

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

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

Create a function and use my query in that function and send 2 parameter in select query.

 

like- 

 

select dbo.functionname(columnname) from tablename. 

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 

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

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

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

 

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

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

 

Use below- 

 

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