question

javed79 avatar image
javed79 asked

Parsing Full Name to First, Mid, Last

I have table with a column called Full Name. In that column I have person's full name as string. Some of the names have 2 parts (First and Last name), some have got 3 Parts (First, Middle and Last) and some have got 4 or more (1 First, 2/3 Middle, 1 Last). How can I separate them in to 1 First, 1 Last and 1 or few Middle name depending on the occurrence? Bradley Jon Jade Evelyn Katarina Johns Louis Jordan James Sam Barnes Thanks.
stringsplit
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Is Bradley Jon stored last name first (as in Bradley, Jon or "Nice to meet you Mr. Bradley" "Please, call me Jon")?
1 Like 1 ·
@SQLShark avatar image
@SQLShark answered
Not a very elegant solution but it is before 08:30 DECLARE @Names TABLE ( Name VARCHAR(50) ) INSERT INTO @Names ( Name ) VALUES ( 'Bradley Jon' ) , ( 'Jade Evelyn Katarina Johns' ) , ( 'Louis Jordan James' ) , ( 'Sam Barnes' ) SELECT Name , SUBSTRING(Name, 1, CHARINDEX(' ', Name)) AS 'First Name' , LTRIM(REPLACE(REPLACE(Name, SUBSTRING(Name, 1, CHARINDEX(' ', Name)), ''), LTRIM(REVERSE(SUBSTRING(REVERSE(Name), 1, CHARINDEX(' ', REVERSE(Name))))), '')) AS 'Middle Names' , LTRIM(REVERSE(SUBSTRING(REVERSE(Name), 1, CHARINDEX(' ', REVERSE(Name))))) AS 'Last Name' FROM @Names
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
It might be early but you are faster than me!
1 Like 1 ·
javed79 avatar image javed79 commented ·
First come first tick. Well done mate. I am still dumb in SQL. I made till the First name and the Last name, but could not figure out how to get middle names :)
1 Like 1 ·
Kev Riley avatar image
Kev Riley answered
T-SQL not the best for string manipulation, but here's a solution... declare @YourTable table (FullName varchar(100)); insert into @YourTable select 'Bradley Jon'; insert into @YourTable select 'Jade Evelyn Katarina Johns'; insert into @YourTable select 'Louis Jordan James'; insert into @YourTable select 'Sam Barnes'; with FullName_cte as ( select charindex(' ',FullName) -1 as FirstNameEnd, len(FullName) - charindex(' ',reverse(FullName)) +2 as LastNameStart, FullName from @YourTable ) select substring(FullName,0,FirstNameEnd+1) as FirstName, case when FirstNameEnd+2 = LastNameStart then '' else substring(FullName, FirstNameEnd+2, LastNameStart-FirstNameEnd-2) end as MiddleName, substring(FullName, LastNameStart, len(FullName)) as LastName from FullName_cte
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

javed79 avatar image javed79 commented ·
Very nice solution. I also use CTE a lot. I find CTE easier to debug.
0 Likes 0 ·
sunithay avatar image
sunithay answered
Declare @name VARCHAR(50) SELECT @name='Sam Barnes Jordan James Johns' SELECT @name=REPLACE(@name,' ',',') while CHARINDEX(',',@name)>0 Begin Declare @pos INT,@part VARCHAR(20) select @pos=CHARINDEX(',',@name) select @part=SUBSTRING(@name,1,@pos-1) Print @part select @name=SUBSTRING(@name,@pos+1,LEN(@name)) if CHARINDEX(',',@name)=0 Print @name End
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image
GPO answered
I hate to be the bearer of bad tidings but I don't think you can reliably unscramble this omelette. The damage appears to have been done to the data before you got it. I say this because it's perfectly reasonably for people to choose to have one or more space characters in any "single" name field. So you could have a surname where the person elects to be known as 'Van Der Haar' or a first name of 'Ann Maree'. What this means is that it's impossible to write code that will just "know" how to split up legitimate names like 'Ann Maree Sally Van Der Haar'. She has three names and three space characters embedded in those names. The hard lesson is don't store names in a concatenated column because it does irreversible damage. (Just don't get me started on developers who tell you that O'Reilly is not a valid name "because it crashes the web page").
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
Wise words and I have witnessed the messes that you describe. The parsing of names from a single column to many is only ever going to be partially correct and you will get exceptions.
3 Likes 3 ·
iainrobertson avatar image iainrobertson commented ·
Emphatically seconded. From the perspective of having had the experience of trying to do it. I still have nightmares...
2 Likes 2 ·
javed79 avatar image javed79 commented ·
Very valid and wise words. But I will just think of the perfect world (perfect naming) and try to achieve something. :)
0 Likes 0 ·
Vamshi09463 avatar image
Vamshi09463 answered
Can any one provide this in function could be great i'm talking about this DECLARE @Names TABLE ( Name VARCHAR(50) ) INSERT INTO @Names ( Name ) VALUES ( 'Bradley Jon' ) , ( 'Jade Evelyn Katarina Johns' ) , ( 'Louis Jordan James' ) , ( 'Sam Barnes' ) SELECT Name , SUBSTRING(Name, 1, CHARINDEX(' ', Name)) AS 'First Name' , LTRIM(REPLACE(REPLACE(Name, SUBSTRING(Name, 1, CHARINDEX(' ', Name)), ''), LTRIM(REVERSE(SUBSTRING(REVERSE(Name), 1, CHARINDEX(' ', REVERSE(Name))))), '')) AS 'Middle Names' , LTRIM(REVERSE(SUBSTRING(REVERSE(Name), 1, CHARINDEX(' ', REVERSE(Name))))) AS 'Last Name' FROM @Names Thanks, Arun
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.