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
1,791 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

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

Assisted Solution

by:sdstuber
sdstuber earned 50 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 75 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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

627 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