CODEWITHSUNDEEP
sqloracle-database
Ron L
Ron L

Reputation: 51

Parse Name to format of "Lastname,FI" Oracle

I have a table with names in the format of "Doe, John" and I need to parse that to "Doe, J". I already have a query written to extract just the last name, but I'm not sure how to modify it to include the comma and first initial. 

SUBSTR(Table.Name,1,INSTR(Table.Name,',',1,1))

Upvotes: 1

Views: 796

Answers (3)

Gary_W
Gary_W

Reputation: 10360

The regexp_replace version, as long as there is only one space in the string as per your example data:

select regexp_replace('Doe, John', '(.* .).*', '\1')

The \1 refers to the matched part of the string in parenthesis, which is all of the string up to and including the last space and the character after it, ignoring the rest.

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

What about this - 

SELECT SUBSTR(Table.Name,1,INSTR(Table.Name,',',1)+2)

Upvotes: 1

Matt
Matt

Reputation: 15061

Use a combinations of NVL, SUBSTR and INSTR.

SELECT NVL(SUBSTR("namefield", 0, INSTR("namefield", ',')+2), "namefield") AS Name
FROM yourtable

Input

Doe, John
Smith, Mark

Output

NAME
Doe, J
Smith, M

SQL Fiddle: http://sqlfiddle.com/#!4/e1f467/7/0

Upvotes: 2

Related Questions