extract data expression

Question

extract data expression

kernal (TechnicalUser)

(OP)

5 Aug 09 16:25

I have 2 fields that I need to use in an expression:

Address   Emerg_Contact
C/O John  blank
C/O Sam   null
C/O Jane  Jones,Sam

Expression needed:

Emerg_Contact has data then take the data after the comma, add a space then take the data before the comma so Jones, Sam becomes Sam Jones, etc.

else if

emerg_contact is null or blank and Address has "C/O" then take the next character it finds after the "C/O" in the Address so the first record it would retrieve John. Note: I mentioned next character after the "C/O" becomes sometimes more than 1 space is entered after the "C/O"

Help is very appreciated.

SantaMufasa (TechnicalUser) · Answer 1 · 2009-08-05 17:16:05

Here is some code:

CODE

SQL> select address, '['||emerg_contact||']' from mytab;

ADDRESS    '['||EMERG_C
---------- ------------
C/O John   [ ]
C/O Sam    []
C/O Jane   [Jones,Sam]

select case when ltrim(emerg_contact) is null then replace(Address,'C/O ')
            when instr(emerg_contact,',')>0   then
                 substr(emerg_contact,instr(emerg_contact,',')+1) ||' '||
                 substr(emerg_contact,1,instr(emerg_contact,',')-1)
       end result
  from mytab;

RESULT
---------------------
John
Sam
Sam Jones

Let us know if this is what you wanted.

santa Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

kernal (TechnicalUser) · Answer 2 · 2009-08-06 17:18:38

thanks dave. I'll send another post as soon as I'm able to try it.

SantaMufasa (TechnicalUser) · Answer 3 · 2009-08-06 17:46:43

Kernal,

Prior to more rigorous testing, I propose adding one more line of code to my previous SQL. The way it was, nothing displays if the EMERG_CONTACT is neither blank, nor null, nor contains a comma. This code resolves that issue:

CODE

SQL> select * from mytab;

ADDRESS    EMERG_CONT
---------- ----------
C/O John
C/O Sam
C/O Jane   Jones,Sam
Whatever   NYC Police

4 rows selected.

select case when ltrim(emerg_contact) is null then replace(Address,'C/O ')
            when instr(emerg_contact,',')>0   then
                 substr(emerg_contact,instr(emerg_contact,',')+1) ||' '||
                 substr(emerg_contact,1,instr(emerg_contact,',')-1)
            else emerg_contact
       end result
  from mytab;

RESULT
---------------------
John
Sam
Sam Jones
NYC Police

4 rows selected.

Let us know how this code works for you.

santa Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

SantaMufasa (TechnicalUser) · Answer 4 · 2009-08-06 17:53:27

BTW, Kernal, are you somewhere on the Wasatch Front?

santa Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

kernal (TechnicalUser) · Answer 5 · 2009-08-06 21:40:25

It works WONDERFUL! Thank you so much. I live in SLC and glad the micro bursts have stopped.

SantaMufasa (TechnicalUser) · Answer 6 · 2009-08-06 22:23:16

I'm glad it works for you, Kernal. (Thanks for the little purple

, as well.)

I work downtown in SLC and live in Sandy. If you'd like to get together for our own Mini (Root)Beery Meet, then let me know via my signature, below.

Regards,

santa Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

Come Join Us!

Posting Guidelines

Jobs

extract data expression