0

I get this from a table John Doe.

What is the best way to split this to have into two variable this :

lastName = 'Doe';
firstName = 'John';
BnJ
  • 1,024
  • 6
  • 18
  • 37

1 Answers1

1

I guess this should work :

SELECT SUBSTR(NameColumn, 1, INSTR(NameColumn,' ',1)) AS firstName,
SUBSTR(NameColumn, INSTR(NameColumn,' ',1)+1) FROM tablName
Codeek
  • 1,624
  • 1
  • 11
  • 20
  • 1
    I might go wrong in indexing and position calculation, so do add suggestive comments and modification (I've kept it open for Wiki community) – Codeek Dec 04 '14 at 14:19
  • Using regular expressions: with tbl as ( select 'John Doe' str from dual ) select regexp_substr(str, '([^ ]*)( |$)', 1, 1, NULL, 1) first_name, regexp_substr(str, '([^ ]*)( |$)', 1, 2, NULL, 1) last_name from tbl; – Gary_W Dec 04 '14 at 18:27
  • thanks :), I am lil bit climsy when it comes to regular expressions. But i'll do a self-study on regex again. Thanks for the comment. – Codeek Dec 05 '14 at 05:44
  • Just another way to approach it. Good to know as nested INSTR/SUBSTR can get ugly. I guess it's up to the developer to decide if it's uglier than the regular expression. heh – Gary_W Dec 05 '14 at 14:04