Home » SQL & PL/SQL » SQL & PL/SQL » Need Query (9.0.4.0)
Need Query [message #345069] Tue, 02 September 2008 04:23 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have string as ' KIRSTEN '. there is a space before 'K' and after 'N'. Now I want to write a query to remove these spaces. Can any one please give me idea for this.

Thank you.
Re: Need Query [message #345074 is a reply to message #345069] Tue, 02 September 2008 04:31 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions199.htm#SQLRF06149

Regards

Raj
Re: Need Query [message #345081 is a reply to message #345069] Tue, 02 September 2008 04:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or LTRIM & RTRIM
Re: Need Query [message #345085 is a reply to message #345081] Tue, 02 September 2008 04:57 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

If I give
select ltrim(' KRISTEN ') from dual; o/p :'kristen '
select rtrim('Kristen ') from dual; o/p : 'kristen'

I am having millions of data like this. So this is not the correct way to use Ltrim and again Rtrim.It's my view....
Please give me any other way..

Thank you.
Re: Need Query [message #345086 is a reply to message #345085] Tue, 02 September 2008 05:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So you are too ignorant to follow S.Rajarams link?

And too ignorant to know the difference between "left" and "right"?

[Updated on: Tue, 02 September 2008 05:02]

Report message to a moderator

Re: Need Query [message #345088 is a reply to message #345085] Tue, 02 September 2008 05:04 Go to previous messageGo to next message
Suwarna
Messages: 10
Registered: August 2008
Location: Mumbai
Junior Member
You may use both the functions together in one query.
ltrim first, then for that do rtrim..
Re: Need Query [message #345092 is a reply to message #345088] Tue, 02 September 2008 05:17 Go to previous messageGo to next message
Suwarna
Messages: 10
Registered: August 2008
Location: Mumbai
Junior Member
but if its just spaces u need to eliminate, better use trim()
Re: Need Query [message #345094 is a reply to message #345086] Tue, 02 September 2008 05:21 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Can I write like this...

Select Replace(' KRISTEN ','','') NAME from dual;

Please let me know.

Thank you.
Re: Need Query [message #345097 is a reply to message #345094] Tue, 02 September 2008 05:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yey, you can write this. It will work perfectly.

*Gives up*
Re: Need Query [message #345099 is a reply to message #345094] Tue, 02 September 2008 05:26 Go to previous messageGo to next message
Suwarna
Messages: 10
Registered: August 2008
Location: Mumbai
Junior Member
nope...

wat if u have 4 spaces before the name and 8 spaces after it?...

Is there some rule at your workplace to not use Trim() ? Laughing
Re: Need Query [message #345100 is a reply to message #345085] Tue, 02 September 2008 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Were you asleep during the lectures about Functions?

Functions can be nested, with the output of one function serving as the input for the next.

If you have functions Fa,Fb and Fc,then you can have Fa(Fb(FC(<value>))) and this will execute, in order, Fc, Fb and then Fa on the value specified.

See if you can think of a way that this knowledge cam be applied to your problem, using only the funtions LTRIM and RTRIM.
Re: Need Query [message #345101 is a reply to message #345094] Tue, 02 September 2008 05:39 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
user71408 wrote on Tue, 02 September 2008 12:21
Can I write like this...

Select Replace(' KRISTEN ','','') NAME from dual;
What happened, when you tried?
SQL> Select Replace(' KRISTEN ','','') NAME from dual;

NAME
---------
 KRISTEN

1 row selected.

SQL> Select Replace(' KRISTEN ',' ','') NAME from dual;

NAME
-------
KRISTEN

1 row selected.

SQL> Select Replace(' KRISTEN NETSIRK ',' ','') NAME from dual;

NAME
--------------
KRISTENNETSIRK

1 row selected.

SQL> 
By the way, what prevents you from using TRIM function, as suggested before?
Previous Topic: Finding a character in a string
Next Topic: SQL Query
Goto Forum:
  


Current Time: Sat Dec 03 13:41:41 CST 2016

Total time taken to generate the page: 0.09061 seconds