Get middle name and first name from name string in SQL Server

Question

I have scenario like

Kumar, Anand 
S,Anand D
D,Ravi,K

Now, I have to extract, first name, middle name and last name from above scenario in SQL Server using one query. Since, to get middle name from the full name was hard and same as first name, so I tried

select 
   CASE 
      WHEN CHARINDEX(' ', REVERSE('D,Ravi,K')) = 0 
        THEN RIGHT('D,Ravi,K', CHARINDEX(',', REVERSE('D,Ravi,K')) - 1) 
      WHEN CHARINDEX(' ', REVERSE('D,Ravi,K')) > 0 
        THEN RIGHT('D,Ravi,K', CHARINDEX(' ', REVERSE('D,Ravi,K')) - 1)
   END AS 'MIDDLE NAME'

But, I'm getting error

Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the right function.

Can anyone help me resolve this?

Any other technique will be appreciated to extract above values.

Your need first name, middle name and last name as 3 diff columns? For data `Kumar, Anand`, Ananad is middle name or last name? — TechDo, May 01 '14 at 04:50
yes.. in three different columns. `Anand` will be consider as `First Name` — , May 01 '14 at 04:59
If you're handling/modelling names, it's a good idea to become an expert on the domain. Chances are, you know less about names, than you think. W3C has a good piece called [Personal names around the world](http://www.w3.org/International/questions/qa-personal-names) that humbled me, when I first read it. — Chris Wesseling, May 01 '14 at 06:52
This question misses basic information. Does your "scenario" make any assumptions about the order in which F,L,M are specified in each line? If not, and it is up you to determine what makes a possible F,L,M, you are guaranteed to make errors in your parsing. Chris already pointed you to the reason. Also read http://stackoverflow.com/questions/5596706/parsing-a-full-name-into-its-constituents and http://stackoverflow.com/questions/17455658/human-name-parsing — Jan Doggen, Apr 09 '15 at 11:17

score 1 · Answer 1 · answered May 01 '14 at 05:02

You need to create a function which gets a string and split it into parts. Following is outline of function:

CREATE FUNCTION splitNames (@name VARCHAR(500), @sep VARCHAR(1))
RETURNS @ret TABLE(idx INT IDENTITY(1,1), part VARCHAR(200))
AS
BEGIN
    WHILE(CHARINDEX(@sep, @name)>0)
    BEGIN
        INSERT INTO @ret (part)
        SELECT SUBSTRING(@name, 1, CHARINDEX(@sep, @name)-1)
        SET @name = SUBSTRING(@name, CHARINDEX(@sep, @name) + LEN(@sep), LEN(@name))
    END
    INSERT INTO @ret (part) 
    SELECT  @name
    RETURN
END

Then you can call it while reading string from your other table or pass string directly as follows:

SELECT f.*
FROM   TABLE_NAME n
CROSS APPLY
(
    SELECT * FROM splitNames(n.name, ',')
)f

Or

SELECT * FROM splitNames('D, Ravi, Anand', ',')

-------------------
| Idx    |  part  |
-------------------
|   1    |  D     |
-------------------
|   2    |  Ravi  |
-------------------
|   3    |  Anand |
-------------------

nope as you need to loop over it. you may try with `CTE` though — TheVillageIdiot, May 02 '14 at 00:39
@TheVillageIdiot When using `,` as separator this works ok, but if I try to use a string like `Name Mid Last` and run the function as `dbo.splitnames('Name Mid Last',' ')` the function apparently never stops running (I've waited 10 minutes and gave up). Anyway, it works really well if I replace ` ` with `,` — Rafael Merlin, Jan 20 '15 at 17:48

score 1 · Answer 2 · edited Apr 19 '16 at 15:33

SELECT 'prabhat ku sahoo'                                                                                                      AS full_name,  
  SUBSTR('prabhat ku sahoo',1,instr('prabhat ku sahoo',' ',1,1))                                                                   AS first_name,
  SUBSTR('prabhat ku sahoo',instr('prabhat ku sahoo',' ',1,1),instr('prabhat ku sahoo',' ',1,2)-instr('prabhat ku sahoo',' ',1,1)) AS middle_name,
  SUBSTR('prabhat ku sahoo',instr('prabhat ku sahoo',' ',1,2))                                                                     AS last_name
FROM dual;

score 0 · Answer 3 · answered May 01 '14 at 04:45

0

I think its proper to create fields for each names:

CREATE TABLE persons_tbl
(
persons_id INT(11),
first_name VARCHAR(100),
middle_name VARCHAR(100),
last_name VARCHAR(100)
);

Then get it with:

SELECT first_name, middle_name, last_name FROM persons_tbl

answered May 01 '14 at 04:45

markandrewkato

112
1
10

I don't have privilege to create new table.. i'm asking `SELECT` query... not `CREATE` – May 01 '14 at 04:48

score 0 · Answer 4 · answered May 01 '14 at 04:55

The error is occuring because the string does not have any spaces in it.

RIGHT('D,Ravi,K',CHARINDEX(' ', REVERSE('D,Ravi,K')) -1)

The CHARINDEX is returning a BLANK or 0.

A negative value cannot be given to a RIGHT function.

You could use REPLACE() to replace the comma's with spaces to have some consistency in the data

score 0 · Answer 5 · edited May 01 '14 at 06:40

0

DECLARE @FullName    VARCHAR(50) = 'D,Ravi,K'

SELECT SUBSTRING(@FullName, 1, CHARINDEX(',', @FullName) - 1) AS [Middle Name],
       SUBSTRING(@FullName, 
           CHARINDEX(',', @FullName) +1,
           LEN(@FullName)- CHARINDEX(',',@Fullname)-2
       ) AS [First Name]

edited May 01 '14 at 06:40

Chris Wesseling

5,917
2
29
67

answered May 01 '14 at 06:04

mohan111

8,245
3
25
46

TechDo · Answer 6 · 2014-05-01T06:26:43.910

Please check my try:

declare @tbl as table(Col nvarchar(100))
insert into @tbl values
('Kumar,Anand'),
('S,Anand D'),
('D,Ravi,K'),
('Anulas')

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

score 0 · Accepted Answer · answered May 03 '14 at 15:53

I did it. Thanks for your help

    declare @name varchar(100)
    set @name='Sharma,Ravi,K'


 select CASE WHEN CHARINDEX(' ',REPLACE(@name,',',' '), CHARINDEX(' ',REPLACE(@name,',',' ')) + 1) >0 THEN
        SUBSTRING(REPLACE(@name,',',' '), CHARINDEX(' ',REPLACE(@name,',',' ')) + 1,(LEN(@name)-CHARINDEX(' ',REVERSE(REPLACE(@name,',',' '))))-CHARINDEX(' ',REPLACE(@name,',',' '))) else SUBSTRING(REPLACE(@name,',',' '), CHARINDEX(' ',REPLACE(@name,',',' ')) + 1,LEN(@name)) END AS 'FIRST NAME'
    ,CASE WHEN CHARINDEX(' ',REPLACE(@name,',',' '), CHARINDEX(' ',REPLACE(@name,',',' ')) + 1) >0 THEN SUBSTRING(REPLACE(@name,',',' '),LEN(@name)-CHARINDEX(' ',REVERSE(REPLACE(@name,',',' ')))+2,LEN(@name)) ELSE ' '
    END AS 'MIDDLE NAME'
    ,
    SUBSTRING(REPLACE(@name,',',' '), 1,CHARINDEX(' ',REPLACE(@name,',',' ')))

score 0 · Answer 8 · edited Jul 08 '16 at 08:16

This will work in case String is FirstName/MiddleName/LastName

Select

DISTINCT NAMES ,

SUBSTRING(NAMES , 1, CHARINDEX(' ', NAMES) - 1) as FirstName,

RTRIM(LTRIM(REPLACE(REPLACE(NAMES,SUBSTRING(NAMES , 1, CHARINDEX(' ', NAMES) - 1),''),REVERSE( LEFT( REVERSE(NAMES), CHARINDEX(' ', REVERSE(NAMES))-1 ) ),'')))as MiddleName,

REVERSE( LEFT( REVERSE(NAMES), CHARINDEX(' ', REVERSE(NAMES))-1 ) ) as LastName

From TABLENAME

score -1 · Answer 9 · edited Apr 17 '15 at 11:07

Here's how to get First_Name, Middle_Name and Last_Name from Full_Name (In place of Full_Name place your attribute Name)

   SELECT 
   LEFT(Full_Name,CHARINDEX(' ',Full_Name + ' ')-1)  AS FIRST_Name,
   Rtrim(substring(Full_Name,CHARINDEX(' ',Full_Name,0)+1,len(Full_Name)-CHARINDEX(' ',Full_Name,0)+1-CHARINDEX(' ', REVERSE(Full_Name)))) Middle_Name,
   Right(Full_Name,ISNULL(NULLIF(CHARINDEX(' ', REVERSE(Full_Name)) - 1,-1), LEN(Full_Name))) AS Last_Name
   FROM [Test].[dbo].[TEST]

score -1 · Answer 10 · edited May 19 '21 at 02:30

-1

I don't know about SQL server, but in MYSQL, I did this to get the first and middle name:

SELECT DISTINCT substring_index(name_column, ' ', 1) as First_name,
substring_index((substring_index(name_column, ' ', -2))," ",1) as Middle_name from table;

edited May 19 '21 at 02:30

C. Peck

3,167
3
16
34

answered May 18 '21 at 19:48

Ashiq Ahamed

1

When you are not sure if it works with the authors system(s) please do not guess sth :) – Timo Reymann May 18 '21 at 21:08

Get middle name and first name from name string in SQL Server

10 Answers10