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

Posted on 2008-10-21
Last Modified: 2008-10-23

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?

          'Marty Rant','chris Motely','Adrian Mac', 'Tony Red','Robert Da', 'Marie Sak', 'Jack Va', 'Larry Engel'))

Question by:Rhonda Carroll
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
LVL 20

Expert Comment

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

Expert Comment

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


Author Comment

by:Rhonda Carroll
ID: 22766885

I tried both and they give me the same error:

Invalid column name.

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.
LVL 74

Assisted Solution

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.
LVL 20

Accepted Solution

gatorvip earned 75 total points
ID: 22767101
Documentation for Oracle 7.3.4

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


Expert Comment

ID: 22773844
use TRIM( Expression / Field Name)

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