substr SQl function

Question

I have a problem in parsing my last name and first name in a table. My code:

SELECT value, 
       substr(value,instr(value,' ',1),instr(value,' ',2)-2) last_name,
  FROM (SELECT 'Matt Ryan, QB' value 
          FROM dual);

If I write 'Andrew Luck, QB' it parses properly the last name. But when I write 'Matt Ryan, QB' it parses just two letters from the last name. I identify it by white space. I don't understand what can be wrong here.

'Matt Ryan, QB'
'Andrew Luck, QB'

@Farhęg it won't be a wild guess to assume he wants to extract the last name ;) — Nir Alfasi
– Nir Alfasi, Commented Mar 27, 2015 at 23:42
@Farhęg in that case, my apologize! I arrived after it was already clear. — Nir Alfasi
– Nir Alfasi, Commented Mar 28, 2015 at 0:49

Ratnakar Malla · Accepted Answer · 2015-03-27 23:53:46Z

1

Please consider using regular expression. I think this should solve your problem.

SELECT value, 
    REGEXP_SUBSTR(value,
                '\w+\s+(\w+\s+\w+|\w+),',1,1,NULL,1) last_name
    FROM ( 
    SELECT 'Matt Ryan, QB' value FROM dual 
    );

Check this fiddle

edited Mar 27, 2015 at 23:53

answered Mar 27, 2015 at 22:09

Ratnakar Malla

714 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ratnakar Malla Over a year ago

Jack, If this works for you. Can you please accept my answer.

Ponder Stibbons Over a year ago

This seems to be the best answer, I checked all with samples from hr.employees (Oracle HR schema). But something is wrong with 'Lex De Haan, QA' ;-)

Ratnakar Malla Over a year ago

I just updated the Reg Exp. This should work for 'Lex De Haan, QA' as well. Please refer to the fiddle.

Ponder Stibbons Over a year ago

For me it looks OK, already upvoted your answer, but I'm not Original Poster, he's the judge here;-)

Jack Over a year ago

Thanks a lot , sorry for the delay .

Nir Alfasi · Accepted Answer · 2015-03-27 21:48:09Z

1

You're misusing the index parameter that you're passing to instr, plus you should remove the comma that comes after "last_name". Try:

SELECT value, 
substr(value, instr(value,' ')+1, instr(value,' ',instr(value,' '))-1) last_name
FROM ( 
  SELECT 'Matt Ryan, QB' value FROM dual 
);

Link to fiddle

answered Mar 27, 2015 at 21:48

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

1 Comment

Jack Over a year ago

Or sorry this stil doesnt work for me . But reg_exp work perfec.

Trinimon · Accepted Answer · 2015-03-27 22:18:14Z

1

The occurrence parameter of INSTR is the fourth parameter, so you have to use

instr(value,' ', 1, 1) -- first space

and

instr(value,' ', 1, 2) -- seconds space

The third parameter of SUBSTR is the number of characters to extract. This is the position of the second space minus the position of the first space - minus one for the comma:

instr(value,' ', 1, 2) -instr(value,' ', 1, 1) -1

So all in all it's:

SELECT value,
      substr(value, instr(value,' ', 1, 1),
                    instr(value,' ', 1, 2) -instr(value,' ', 1, 1) -1) last_name
 FROM (SELECT 'Matt Ryan, QB' value 
         FROM dual);

Check this Fiddle here.

answered Mar 27, 2015 at 22:18

Trinimon

14k9 gold badges46 silver badges61 bronze badges

Collectives™ on Stack Overflow

substr SQl function

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related