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