Toad World® Forums

Decode Function?

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…

  1. For first name - the instr finds the position of the space, and then the substr extracts from the first character to the space.

  2. 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

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Decode Function?

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

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Decode Function?

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?

RE: 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?

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?

RE: Decode Function?

Reply by Dennis Paulus

The easiest way to do this is substr and then using instr for th position…

  1. For first name - the instr finds the position of the space, and then the substr extracts from the first character to the space.

  2. 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?

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.

http://www.toadworld.com/platforms/oracle/b/weblog/archive/2009/10/22/good-database-design-it-39-s-never-an-accident.aspx

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

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Decode Function?

RE: 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

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Decode Function?

RE: 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