Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1802
  • Last Modified:

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

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
Rhonda Carroll
Asked:
Rhonda Carroll
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
gatorvipCommented:
upper(trim(c.X_PRIM_SALES_FIRST_NAME)) ||' '|| upper(trim(c.X_PRIM_SALES_LAST_NAME)) in ('JON DOE', .....
0
 
sdstuberCommented:
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
 
Rhonda CarrollAuthor Commented:
Hi,

I tried both and they give me the same error:

Invalid column name.

R
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rhonda CarrollAuthor Commented:
Also, the version of oracle is 7.6.
0
 
sdstuberCommented:
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
 
gatorvipCommented:
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
 
divyeshhdoshiCommented:
use TRIM( Expression / Field Name)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now