CODEWITHSUNDEEP
sqloracle-database
Taoqir
Taoqir

Reputation: 189

Separate last name from a full name

I want to display my data in this format:

First_Name  Mid_Name  Last_Name
All         Ahmad     Khan

This is my code

select substr(customer_name1, 0, instr(customer_name1, ' ')-1) as first_name
     , substr(customer_name1, instr(customer_name1, ' ')+1) as mid_name 
     , ... as last_name
  from sttm_customers 
 where customer_name1 = 'ALI AHMAD KHAN'

Can anyone help me with how I should add last_name in my report?

Upvotes: 1

Views: 14081

Answers (5)

sudharsonn s
sudharsonn s

Reputation: 51

To seperate the firstname, middlename, lastname from full name.

It worked oracle 11g. Consider table username with one field call uname which contains following names for example.

sachin ramesh tendulkar
shiva sudharson s
shiva kumar s
shiva nandhan s
shiva selvan s

To split the name as firstname, middlename, lastname below query works.

select 
substr(uname,1, instr(uname,' ')) as firstname, 
substr(uname, instr(uname, ' '), instr(uname, ' ', 1, 2)-instr(uname, ' ')) as middlename,
substr(uname, instr(uname, ' ',1,2), length(uname)) as lastname from username;

Result will be like:

sachin   ramesh  tendulkar
shiva    sudharson   s
shiva    kumar   s
shiva    nandhan     s
shiva    selvan  s

Upvotes: 0

Andy Lester
Andy Lester

Reputation: 93676

You can't do it reliably, because you don't have a reliable way to know what is a first name and what is a last name. The examples above show splitting on whitespace, but that doesn't do it reliably.

If you have "John Fitzgerald Kennedy", then it's easy. Your first name is "John", middle is "Fitzgerald" and last is "Kennedy". No problem.

What if you have "Mary Ann Summers"? First name should be "Mary Ann" and last name is "Summers", but splitting on whitespace doesn't know that. Splitting on whitespace gives you "Mary", "Ann" and "Summers", which is wrong.

In the opposite direction, what if you have "Eddie Van Halen"? "Eddie" is first name, and "Van Halen" is the last name, but breaking on whitespace would give you "Eddie", "Van" and "Halen", which is wrong.

For that matter, you could have "Mary Ann Van Halen", and then what do you do?

Bottom line: If you haven't captured your first and last name as separate data points, you can't reliably split them apart.

Upvotes: 4

nayef harb
nayef harb

Reputation: 753

Put -1 as the starting postions and the sub string will start counting from the end and then

find the instr for the white space hope it helps

Upvotes: 0

Taryn
Taryn

Reputation: 247690

This is a bit ugly, but it will separate each piece:

with data(customer_name1) as
(
    select 'ALI AHMAD KHAN'
    from dual
),
first(firstname, customer_name1) as
(
    select substr(customer_name1, 0, instr(customer_name1, ' ')-1) as first_name,
        ltrim(replace(customer_name1, substr(customer_name1, 0, instr(customer_name1, ' ')-1), '')) customer_name1
    from data
),
middle(firstname, middlename, customer_name1) as
(
    select firstname, 
        substr(customer_name1, 0, instr(customer_name1, ' ')-1) middlename,
        ltrim(replace(customer_name1, substr(customer_name1, 0, instr(customer_name1, ' ')-1), '')) customer_name1
     from first
)
select firstname, middlename, customer_name1 as LastName
from middle

See SQL Fiddle with Demo

Upvotes: 0

DazzaL
DazzaL

Reputation: 21973

SQL> select substr(customer_name1, 0, instr(customer_name1, ' ')-1) as first_name
  2       , substr(customer_name1, instr(customer_name1, ' ')+1,
  3                instr(customer_name1, ' ', -1, 1) - instr(customer_name1, ' ') - 1) as mid_name
  4       , substr(customer_name1, instr(customer_name1, ' ', -1) + 1) last_name
  5    from (select 'ALI AHMAD KHAN' customer_name1 from dual)
  6  /

FIR MID_N LAST
--- ----- ----
ALI AHMAD KHAN

this copes with the name having multiple middle names.

Upvotes: 1

Related Questions