Home » SQL & PL/SQL » SQL & PL/SQL » Trim the spaces between the words in oracle
Trim the spaces between the words in oracle [message #291347] Thu, 03 January 2008 23:39 Go to next message
inforacle
Messages: 21
Registered: December 2006
Junior Member
hi,

Need ur help.How to trim the spaces between the words in oracle

EX : the word is
'HAI  HELLO'

the o/p should be
'HAI HELLO'
Single space should be there between the words

[EDITED by LF: added [pre] tags to emphasize the requirement]

[Updated on: Fri, 04 January 2008 00:56] by Moderator

Report message to a moderator

Re: Trim the spaces between the words in oracle [message #291373 is a reply to message #291347] Fri, 04 January 2008 00:54 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use regular expressions:
SQL> SELECT
  2  REGEXP_REPLACE('hai        hello', '[[:blank:]]+', ' ')
  3  FROM dual
  4  /

REGEXP_RE
---------
hai hello
Re: Trim the spaces between the words in oracle [message #291382 is a reply to message #291347] Fri, 04 January 2008 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are not in 10g, read the following thread in AskTom: Replacing Multiple Blanks by single blanks in text

Regards
Michel
Re: Trim the spaces between the words in oracle [message #291478 is a reply to message #291382] Fri, 04 January 2008 05:54 Go to previous messageGo to next message
inforacle
Messages: 21
Registered: December 2006
Junior Member
Thanks Cool
Re: Trim the spaces between the words in oracle [message #291547 is a reply to message #291347] Fri, 04 January 2008 15:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hey, a nice use of REGULAR EXPRESSIONS. Maybe they are good for something afterall.

Thanks, Kevin
Re: Trim the spaces between the words in oracle [message #291550 is a reply to message #291547] Fri, 04 January 2008 15:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But not for performances Laughing
(at least in 10g, they badly wrote the regexp code, hopefully they changed it in 11g, I didn't test it, I hope it is better).

Regards
Michel
Re: Trim the spaces between the words in oracle [message #291558 is a reply to message #291347] Fri, 04 January 2008 17:07 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I did not know that, thanks. Too bad, the code I wrote for doing this ain't any better so was hoping to find a better solution.

Kevin
Previous Topic: Function based index creation.
Next Topic: Sql to Update common rows in 2 tables
Goto Forum:
  


Current Time: Thu Dec 08 20:07:07 CST 2016

Total time taken to generate the page: 0.09377 seconds