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.
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
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
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
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").
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.
Emphatically seconded. From the perspective of having had the experience of trying to do it. I still have nightmares...
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
19 People are following this question.
