Need Query [message #345069] |
Tue, 02 September 2008 04:23  |
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 #345085 is a reply to message #345081] |
Tue, 02 September 2008 04:57   |
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 #345088 is a reply to message #345085] |
Tue, 02 September 2008 05:04   |
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 #345094 is a reply to message #345086] |
Tue, 02 September 2008 05:21   |
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 #345100 is a reply to message #345085] |
Tue, 02 September 2008 05:31   |
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  |
flyboy
Messages: 1903 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?
|
|
|