Home » SQL & PL/SQL » SQL & PL/SQL » Replace Leading Spaces
Replace Leading Spaces [message #244393] Tue, 12 June 2007 10:33 Go to next message
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member

Oracle 9i, so RegEx stuff won't help. What is the best way to replace leading spaces in a select ?

I am trying to create an html document, and would like to replace leading spaces with &nbsp.

What would be the most efficient way of accomplishing this?


Re: Replace Leading Spaces [message #244398 is a reply to message #244393] Tue, 12 June 2007 11:17 Go to previous message
Michel Cadot
Messages: 63929
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do something like that:
SQL> with data as (select '    something with leading space' d from dual),
  2       step1 as (select length(d)-length(ltrim(d))+1 idx from data)
  3  select replace(substr(d,1,idx-1),' ',' ') || substr(d,idx) d
  4  from data, step1
  5  /
    something with leading space

1 row selected.

Previous Topic: FIXED ORA-02180 when Creating a Temporary Tablespace
Next Topic: regarding undefined object type
Goto Forum:

Current Time: Wed Oct 26 18:31:39 CDT 2016

Total time taken to generate the page: 0.13149 seconds