I have a field that has full name, trying to figure out the best way (easiest) to de-concatenate the field to first name saved to a new field and last name saved to another…any thoughts?
The easiest way to do this is substr and then using instr for th position…
-
For first name - the instr finds the position of the space, and then the substr extracts from the first character to the space.
-
For last name - the instr finds the position of the space, and then the substr extracts from the space to the last character.
Here is an example…
select
substr(‘Joe Smoe’,1,instr( ‘Joe
Smoe’,’ ',1))
first_name,
substr(‘Joe Smoe’,instr( ‘Joe
Smoe’,’ ',1)+1)
last_name
from dual;
From: hani.chahelli [mailto:bounce-hanichahelli@toadworld.com]
Sent: Wednesday, June 26, 2013 12:14 PM
Subject: [Toad for Oracle - Discussion Forum] Decode Function?
Thread created by hani.chahelli
I have a field that has full name, trying to figure out the best way (easiest) to de-concatenate the field to first name saved to a new field and last name saved
to another…any thoughts?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
You can do this pretty easily with the SUBSTR and INSTR functions, but there may be some data that will cause problems, like two name first names, such as “Mary
Ann”
From: hani.chahelli [mailto:bounce-hanichahelli@toadworld.com]
Sent: Wednesday, June 26, 2013 11:13 AM
Subject: [Toad for Oracle - Discussion Forum] Decode Function?
Thread created by hani.chahelli
I have a field that has full name, trying to figure out the best way (easiest) to de-concatenate the field to first name saved to a new field and last name saved
to another…any thoughts?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
Thanks John. I see that full names can get ugly…
Regard’s,
Hani
From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]
Sent: Wednesday, June 26, 2013 1:07 PM
To: toadoracle@toadworld.com
Subject: RE: [Toad for Oracle - Discussion Forum] Decode Function?
Reply by John Dorlon
You can do this pretty easily with the SUBSTR and INSTR functions, but there may be some data that will cause problems, like two name first names, such as “Mary Ann”
Click to show quoted text
From: hani.chahelli [mailto:bounce-hanichahelli@toadworld.com]
Sent: Wednesday, June 26, 2013 11:13 AM
To: toadoracle@toadworld.com
Subject: [Toad for Oracle - Discussion Forum] Decode Function?
Thread created by hani.chahelli
I have a field that has full name, trying to figure out the best way (easiest) to de-concatenate the field to first name saved to a new field and last name saved to another…any thoughts?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from General notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from General notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
This communication is from Navigant Consulting Inc. E-mail text or attachments may contain information which is confidential and may also be privileged. This communication is for the exclusive use of the intended recipient(s). If you have received this communication in error, please return it with the title “received in error” to NCISecurity@navigant.com, and then delete the email and destroy any copies of it. In addition, this communication is subject to, and incorporates by reference, additional disclaimers found in Navigant Consulting’s “Email Disclaimer” section at www.Navigant.com.
Navigant Consulting, Inc.
Company Registration Number: UK Ltd. 3641719
Registered in Delaware, USA
Registered Office Address: 30 South Wacker Drive, Suite 3400, Chicago, Illinois 60606
Hi Dennis,
Thanks for the sample script…got it…
Hani
From: Dennis Paulus [mailto:bounce-Dennis_Paulus@toadworld.com]
Sent: Wednesday, June 26, 2013 1:01 PM
To: toadoracle@toadworld.com
Subject: RE: [Toad for Oracle - Discussion Forum] Decode Function?
Reply by Dennis Paulus
The easiest way to do this is substr and then using instr for th position…
-
For first name - the instr finds the position of the space, and then the substr extracts from the first character to the space.
-
For last name - the instr finds the position of the space, and then the substr extracts from the space to the last character.
Here is an example…
select substr(‘Joe Smoe’,1,instr(‘Joe Smoe’,’ ',1)) first_name,
substr('Joe Smoe',instr('Joe Smoe',' ',1)+1) last_name
from dual;
Click to show quoted text
From: hani.chahelli [mailto:bounce-hanichahelli@toadworld.com]
Sent: Wednesday, June 26, 2013 12:14 PM
To: toadoracle@toadworld.com
Subject: [Toad for Oracle - Discussion Forum] Decode Function?
Thread created by hani.chahelli
I have a field that has full name, trying to figure out the best way (easiest) to de-concatenate the field to first name saved to a new field and last name saved to another…any thoughts?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from General notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from General notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
This communication is from Navigant Consulting Inc. E-mail text or attachments may contain information which is confidential and may also be privileged. This communication is for the exclusive use of the intended recipient(s). If you have received this communication in error, please return it with the title “received in error” to NCISecurity@navigant.com, and then delete the email and destroy any copies of it. In addition, this communication is subject to, and incorporates by reference, additional disclaimers found in Navigant Consulting’s “Email Disclaimer” section at www.Navigant.com.
Navigant Consulting, Inc.
Company Registration Number: UK Ltd. 3641719
Registered in Delaware, USA
Registered Office Address: 30 South Wacker Drive, Suite 3400, Chicago, Illinois 60606
I’ve also coded versions that handle multiple-word first names by looking for the
last space instead of the first, and then I had to code for Jim Jones Jr., Hans von der Aahe, Jost de Ruiter, etc. with multiple-word last names….
Nate Schroeder
This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled
to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and
all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.
All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its
subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.
Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying
this e-mail or any attachment.
The information contained in this email may be subject to the export control laws and regulations of the United States, potentially
including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of
Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all
applicable U.S. export laws and regulations.
Nathan, can you expalin your logic here, because you don’t know if a full name has a multiple first name or multiple last name when you run a script for thousands of names, unless you do it individually…
Hani,
There are actually a lot of ways to do this. Obviously, the easiest would be to use PL/SQL to determine this information, and then make the code as simple
or complicated as you wanted, based on your data.
Here is another expanded SQL example from the one I sent you before. Again, you’d probably be using PL/SQL instead, but this is a starter for ya
J.
number_of_names: counts the number of spaces, adds one, and that’s the count of different names in the full name string. You could then break it down from
there based on your data, and determine what to do with each.
The other fields are self explanatory. Basically, it comes down to your data and the way it’s entered, but perhaps this will help a little when narrowing
it down.
select
length(‘Larry Mo Curly Joe’)
length(replace(‘Larry Mo Curly Joe’, ’
'))+1
number_of_names,
substr(‘Larry Mo Curly Joe’,1,instr( ‘Larry
Mo Curly Joe’,’ ',1))
first_name,
substr(‘Larry Mo Curly Joe’,instr( ‘Larry
Mo Curly Joe’,’ ‘,1)+1,(instr( ‘Larry
Mo Curly Joe’,’ ',1,2)
instr(‘Larry Mo Curly Joe’, ’
',1)-1))
second_name,
substr(‘Larry Mo Curly Joe’,instr( ‘Larry
Mo Curly Joe’,’ ‘,1,2)+1,(instr( ‘Larry
Mo Curly Joe’,’ ',1,3)
instr(‘Larry Mo Curly Joe’, ’
',1,2)-1))
third_name,
substr(‘Larry Mo Curly Joe’,instr( ‘Larry
Mo Curly Joe’,’ ',-1,1)+1)
last_name
from dual;
Thanks,
Dennis
Dennis, Thanks for the feedback…Love the explanation.
Just so people reading this thread know - this design violates 1st normal form. And you cannot be in 2nd or 3rd normal form if you;re not in 1st.
www.toadworld.com/…/normalization-in-non-gobbledygook-language.aspx
This whole discussion is the reason I wrote this blog years ago – it’s hard to work around poor design. And prior to 11g virtual columns you could not index
such poor designs.
Bert Scalzo
Engr Tech Principal Engineer
Dell | Information Management
office
+1 469 888 5302
Quest Software is now part of Dell
From: Dennis Paulus [mailto:bounce-Dennis_Paulus@toadworld.com]
Sent: Wednesday, June 26, 2013 3:36 PM
Subject: RE: [Toad for Oracle - Discussion Forum] Decode Function?
Reply by Dennis Paulus
Hani,
There are actually a lot of ways to do this. Obviously, the easiest would be to use PL/SQL to determine this information, and then make the code as simple
or complicated as you wanted, based on your data.
Here is another expanded SQL example from the one I sent you before. Again, you’d probably be using PL/SQL instead, but this is a starter for ya
J.
number_of_names: counts the number of spaces, adds one, and that’s the count of different names in the full name string. You could then break it down from
there based on your data, and determine what to do with each.
The other fields are self explanatory. Basically, it comes down to your data and the way it’s entered, but perhaps this will help a little when narrowing it
down.
select
length(‘Larry Mo Curly Joe’)
length(replace(‘Larry Mo Curly Joe’, ’
'))+1
number_of_names,
substr(‘Larry Mo Curly Joe’,1,instr( ‘Larry
Mo Curly Joe’,’ ',1))
first_name,
substr(‘Larry Mo Curly Joe’,instr( ‘Larry
Mo Curly Joe’,’ ‘,1)+1,(instr( ‘Larry
Mo Curly Joe’,’ ',1,2)
instr(‘Larry Mo Curly Joe’, ’
',1)-1))
second_name,
substr(‘Larry Mo Curly Joe’,instr( ‘Larry
Mo Curly Joe’,’ ‘,1,2)+1,(instr( ‘Larry
Mo Curly Joe’,’ ',1,3)
instr(‘Larry Mo Curly Joe’, ’
',1,2)-1))
third_name,
substr(‘Larry Mo Curly Joe’,instr( ‘Larry
Mo Curly Joe’,’ ',-1,1)+1)
last_name
from dual;
Thanks,
Dennis
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
Here’s what I wound up doing – as you say, doing it somewhat individually for all the name variations that happened to be in this particular dataset:
SELECT SUBSTR(RTRIM(u.full_name), u.last_name_start, 999) last_name
, SUBSTR(u.full_name,1,u.last_name_start-1))) first_name
FROM (SELECT full_name
, CASE
WHEN INSTR(UPPER(full_name), ' VAN ')>0
THEN INSTR(UPPER(full_name), ' VAN ') +1
WHEN INSTR(UPPER(full_name), ' III')>0
OR INSTR(UPPER(full_name), ' DE ')>0
OR INSTR(UPPER(full_name), ' MC ')>0
OR INSTR(UPPER(full_name), ' JR')>0
THEN INSTR(RTRIM(full_name), ' ', -1,2) +1
ELSE INSTR(RTRIM(full_name), ' ', -1) +1
END last_name_start
FROM <source>) u
Nate Schroeder
US Row Crops IT Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. G3WB - Saint Louis, MO - 63167
314-694-2592
From: hani.chahelli [mailto:bounce-hanichahelli@toadworld.com]
Sent: Wednesday, June 26, 2013 2:16 PM
Subject: RE: [Toad for Oracle - Discussion Forum] Decode Function?
Reply by hani.chahelli
Nathan, can you expalin your logic here, because you don’t know if a full name has a multiple first name or multiple last name when you run a script for thousands
of names, unless you do it individually…
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled
to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and
all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.
All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its
subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of “Viruses” or other “Malware”.
Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying
this e-mail or any attachment.
The information contained in this email may be subject to the export control laws and regulations of the United States, potentially
including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of
Treasury, Office of Foreign Asset Controls (OFAC). As a recipient of this information you are obligated to comply with all
applicable U.S. export laws and regulations.
I’m suprised that regular expressions haven’t been mentioned, ala https://forums.oracle.com/thread/1013917
Seems to be a prime example…
Rich