1

I have a legacy column in my table which stores name and surname values together. For Example: Mike Huge Muyra or John Kutgira.

I need to split column by whitespace.

Result must be like that (Last word is surname, others are name)

Name : Mike Huge,  Surname : Muyra
Name : John,       Surname : Kutgira

I have been researching for two hours. (REGEXP_SUBSTR is looking cool) But I can not find the solution.

Tugrul
  • 1,760
  • 4
  • 24
  • 39

3 Answers3

2

You can use regular expressions for this. However, because the rule involves the last space perhaps regular string operations are fine:

select trim(substr(legacy, 1 - instr(reverse(legacy), ' '))) as surname,
       trim(substr(legacy, 1, length(legacy) - instr(reverse(legacy), ' '))) as restname

You might want to wrap these in case legacy like '% %' just to be sure they have a space.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Try finding out the last index of space (' ') character and then using substring (twice I guess for first and last part) to get what you want.

Here is an answer for the last index of part.

Community
  • 1
  • 1
Ravindra HV
  • 2,558
  • 1
  • 17
  • 26
1
select trim(substr(:p_str, 1, instr(:p_str, ' ', -1)-1)) first_name,
       trim(substr(:p_str, instr(:p_str, ' ', -1)+1)) surname 
  from dual;
Kevin Crowley
  • 11
  • 1
  • Your answer certainly is worth a little explanation. Kindly refer to http://stackoverflow.com/help/how-to-answer . Comments would help create searchable content. – J. Chomel Apr 04 '17 at 06:23