Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

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.
Helpful Member!  SantaMufasa (TechnicalUser)
5 Aug 09 17:16
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.

santaMufasa
(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) (OP)
6 Aug 09 17:18
thanks dave. I'll send another post as soon as I'm able to try it.
SantaMufasa (TechnicalUser)
6 Aug 09 17:46
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.

santaMufasa
(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)
6 Aug 09 17:53
BTW, Kernal, are you somewhere on the Wasatch Front?

santaMufasa
(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) (OP)
6 Aug 09 21:40
It works WONDERFUL! Thank you so much. I live in SLC and glad the micro bursts have stopped.
SantaMufasa (TechnicalUser)
6 Aug 09 22:23
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,
 

santaMufasa
(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."

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close