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,773 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 73

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

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

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now