Need splite Full_Name field to 3 columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need splite Full_Name field to 3 columns

Author

Topic

aslu63
Starting Member

7 Posts

Posted - 2012-03-15 : 16:09:35

Hello All,
I have 7 different scenarios in my Full_Name field.
Example:
--1 SMITH;ELDON
--2 SMITH,ELDON
--3 SMITH,ELDON WILLIAM
--4 E W SMITH
--5 ELDON W
--6 ELDON W SMITH
--7 ELDON SMITH
I need split to Last, First and Middle name
How I can do it?
The most concern when I have First Middle Last Name, How I can say SQL after first space middle name or Last Name?

Thank you in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 16:13:08

so what should be output for each of above cases? can you show corresponding parts for each?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aslu63
Starting Member

7 Posts

Posted - 2012-03-15 : 16:20:43

I have 3 columns
and output should be
for last_name : SMITH
first_Name: ELDON
Middle_N :

last_name : SMITH
first_Name: ELDON
Middle_N :

last_name : SMITH
first_Name: ELDON
Middle_N : WILLIAM

last_name : SMITH
first_Name: E
Middle_N : W

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 16:33:20

so its not consistent. then i feel you need to break it up to groups and then apply logic for each one to get correct output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 16:38:47

say for one group you can do like below


SELECT CASE WHEN LEN(PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),2)) >1 
THEN PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),2) 
ELSE PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),1)
END AS LastName,
CASE WHEN LEN(PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),2)) >1 
THEN PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),1) 
ELSE PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),2)
END AS FirstName,
NULL AS MiddleName
FROM table
WHERE LEN(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'))-LEN(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'))=1

and next one like

SELECT CASE WHEN LEN(PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),3)) >1 
THEN PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),3) 
ELSE PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),1)
END AS LastName,
CASE WHEN LEN(PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),3)) >1 
THEN PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),1) 
ELSE PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),3)
END AS FirstName,
PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),2) AS MiddleName
FROM table
WHERE LEN(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'))-LEN(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'))=2

etc...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aslu63
Starting Member

7 Posts

Posted - 2012-03-16 : 09:13:48

I get error msg: The replace function requires 3 argument(s)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 11:18:53

there was a typo. use this


SELECT CASE WHEN LEN(PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),2)) >1 
THEN PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),2) 
ELSE PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),1)
END AS LastName,
CASE WHEN LEN(PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.')),' ','.'),2)) >1 
THEN PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),1) 
ELSE PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),2)
END AS FirstName,
NULL AS MiddleName
FROM table
WHERE LEN(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'))-LEN(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'))=1

and next one like

SELECT CASE WHEN LEN(PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),3)) >1 
THEN PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),3) 
ELSE PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),1)
END AS LastName,
CASE WHEN LEN(PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),3)) >1 
THEN PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),1) 
ELSE PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),3)
END AS FirstName,
PARSENAME(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'),2) AS MiddleName
FROM table
WHERE LEN(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'))-LEN(REPLACE(REPLACE(REPLACE(Full_Name,';','.'),',','.'),' ','.'))=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources