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,788 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.

752 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