asked
Split Full Name to FN, MN and LN
Hi, I have a column (Full Name) which needs to split into First Name, Middle Name and Last Name. So can any one of you provide me function to do that. Issue is here there is no limit to the middle names like a person name can be like (abc def ghi jkl pqrs). In this First name should be populated as abc and last name is pqrs and rest is middle name so for this i need function. Name have spaces in between them. So far i have written with select statement and it working fine but when i'm trying to put it into the function i'm not able to do it, Can any one of you provide this would be great. declare @tbl as table(Col nvarchar(100)) insert into @tbl values ('Kumar,Anand'), ('S Anand D'), ('D Ravi K'), ('Anulas'), ('David Kennedy Kane Williams'), ('David K. Williams'), ('David Kennedy Kane Williams abc def ghi jkl mno pqrst') select Cola, FN, REPLACE(REPLACE(Col, ' '+FN,''), LN+' ','') MN, LN from(select *, LEFT(Col, CHARINDEX(' ', col, 1)-1) LN, REVERSE(LEFT(REVERSE(Col), CHARINDEX(' ', REVERSE(Col), 1)-1)) FN from(select Col Cola, case LEN(Col)-LEN(REPLACE(Col,' ','')) when 0 then ' '+Col when 1 then REPLACE(Col, ' ',' ') else Col end Col from @tbl )x )y Thanks
Will it always be a case of: FirstName (always one word, no spaces) MiddleName (always something, maybe many separations with spaces) LastName (always one word, no spaces)
CREATE FUNCTION dbo.parse_name ( @Full_Name AS NVARCHAR (100) ) RETURNS TABLE AS RETURN SELECT tvT.Col AS full_name ,caFL.total_spaces ,(CASE WHEN caFL.total_spaces < 1 THEN N'' ELSE LEFT (caLR.Col, caFL.first_space) END) AS first_name ,(CASE WHEN caFL.total_spaces < 2 THEN N'' ELSE SUBSTRING (caLR.Col, caFL.first_space + 2, LEN (caLR.Col) - caFL.first_space - caFL.last_space - 2) END) AS middle_name ,(CASE WHEN caFL.total_spaces < 1 THEN N'' ELSE RIGHT (caLR.Col, caFL.last_space) END) AS last_name FROM ( SELECT @Full_Name AS Col ) tvT CROSS APPLY ( SELECT LTRIM (RTRIM (tvT.Col)) AS Col ) caLR CROSS APPLY ( SELECT LEN (caLR.Col) - LEN (REPLACE (caLR.Col, N' ', N'')) AS total_spaces ,CHARINDEX (N' ', caLR.Col) - 1 AS first_space ,CHARINDEX (N' ', REVERSE (caLR.Col)) - 1 AS last_space ) caFL GO declare @tbl as table(Col nvarchar(100)) insert into @tbl values ('Kumar,Anand'), ('S Anand D'), ('D Ravi K'), ('Anulas'), ('David Kennedy Kane Williams'), ('David K. Williams'), ('David Kennedy Kane Williams abc def ghi jkl mno pqrst') SELECT * FROM @tbl tvT OUTER APPLY dbo.parse_name (tvT.Col) tvfPN
Sorry, the formatting when it posts is awful. Regardless, would something like this work for you?
DECLARE @Names TABLE ( Name VARCHAR(50) ) INSERT INTO @Names ( Name ) VALUES ( 'John Franklin' ) , ( 'Jade Evelyn Katarina Johns' ) , ( 'Louis Jordan James' ) , ( 'Ram Kumar' ) 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
20 People are following this question.
