Script to Separate-First-Middle-Last-Name

,

This Script is written to Separate First , Middle & Last Name from column which is having Fullname(Fname+MiddleName+Surname)

/* Script to separate first ,Middle &  last Name from FullName in one column */

USE	Northwind
go

CREATE	TABLE	Test
(
FullName	VARCHAR(40)
)

INSERT	INTO	Test
	VALUES
	(
	'Mathew John Davis'
	)


/* Variables Declaration */

DECLARE @var1 INT
DECLARE @var2 INT
DECLARE @var3 INT


/* select position for first blank space from column */

SELECT	@var1	=	CHARINDEX(' ',FullName)
	FROM	Test

/* select first Name as follows from first position of column to first blank space */

SELECT	SUBSTRING(FullName,1,@var1-1)
	FROM	Test

/* select position for second blank space from column */


select	@var2	=	CHARINDEX(' ', SUBSTRING(FullName,@var1+1,LEN(FullName)))
	FROM	Test

/* select Middle Name as follows from  position of first blank space to second blank space */

select	SUBSTRING(FullName,@var1+1,@var2-1) 
	FROM	Test


/* select Last Name as rest of the string from second blank space to length of string */


SELECT	SUBSTRING(FullName,@var1+@var2+1,LEN(FullName)) 
	FROM	Test

Rate

Share

Share

Rate