Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I use trim in oracle to remove blank spaces before and after names in my query?

Posted on 2008-10-21
7
Medium Priority
?
1,798 Views
Last Modified: 2008-10-23
Hi,

Below is a condition in my where clause but I need to use the function trim to remove any spaces before and after both first and last names(otherwise my report isn't picking up the names correctly, there a few in the database with spaces causing problems). Also, if you could include upper as well that would be great!!  can you please show me how to correctly use it?

 and (c.X_PRIM_SALES_FIRST_NAME||' '||c.X_PRIM_SALES_LAST_NAME in ('Jon Doe',
          'Marty Rant','chris Motely','Adrian Mac', 'Tony Red','Robert Da', 'Marie Sak', 'Jack Va', 'Larry Engel'))

Thanks,
R
0
Comment
Question by:Rhonda Carroll
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 20

Expert Comment

by:gatorvip
ID: 22766689
upper(trim(c.X_PRIM_SALES_FIRST_NAME)) ||' '|| upper(trim(c.X_PRIM_SALES_LAST_NAME)) in ('JON DOE', .....
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22766706
If you are converting your first and last names to upper and trimming them, your "in" clause must be in all caps and trimmed too.  You did already have it trimmed but you need to capitalize them too
 AND (UPPER(TRIM(c.x_prim_sales_first_name)) || ' ' || UPPER(TRIM(c.x_prim_sales_last_name)) IN
                  ('JON DOE',
                   'MARTY RANT',
                   'CHRIS MOTELY',
                   'ADRIAN MAC',
                   'TONY RED',
                   'ROBERT DA',
                   'MARIE SAK',
                   'JACK VA',
                   'LARRY ENGEL'))

Open in new window

0
 

Author Comment

by:Rhonda Carroll
ID: 22766885
Hi,

I tried both and they give me the same error:

Invalid column name.

R
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Rhonda Carroll
ID: 22766911
Also, the version of oracle is 7.6.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 22766991
7.6 can't be correct, the last release of version 7 is 7.3.4

Can you post your entire query and the table descriptions.
0
 
LVL 20

Accepted Solution

by:
gatorvip earned 300 total points
ID: 22767101
Documentation for Oracle 7.3.4

http://download.oracle.com/docs/cd/A57673_01/DOC/dcommon/oin/indexj.htm

I don't see a trim() function in there. Use ltrim(rtrim(upper(  <your_column> ))) instead.



0
 
LVL 6

Expert Comment

by:divyeshhdoshi
ID: 22773844
use TRIM( Expression / Field Name)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question