Skip to Main Content
  • Questions

  • Insert space in a string after x characters

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Krishna.

Asked: May 01, 2009 - 5:03 pm UTC

Last updated: May 02, 2009 - 12:45 pm UTC

Version: oracle 9.2.0.7.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

In Oracle 9i, is there a way to insert spaces after x number of characters in a single select statement?

For the example below, I need to insert a space after every 3rd character -

Input String: testin123fortom
Output String needs to be: tes tin 123 for tom

Thanks in advance,
Krishna

and Tom said...

this'll be easy in 10g

ops$tkyte%ORA11GR1> select regexp_replace( 'testing123fortom', '(...)', '\1 ' ) from dual;

REGEXP_REPLACE('TESTI
---------------------
tes tin g12 3fo rto m



http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm#SQLRF06302


In 9i, a tad more difficult...
ops$tkyte%ORA9IR2> create table t ( x int, y varchar2(20) );

Table created.

ops$tkyte%ORA9IR2> insert into t values ( 1, 'testing123fortom' );

1 row created.

ops$tkyte%ORA9IR2> column newy format a30
ops$tkyte%ORA9IR2> select t.*,
  2         trim(
  3         substr( y,  1, 3 ) || ' ' || substr( y,  4, 3 ) || ' ' || substr( y,  7, 3 ) || ' ' ||
  4         substr( y, 10, 3 ) || ' ' || substr( y, 13, 3 ) || ' ' || substr( y, 16, 3 ) || ' ' ||
  5             substr( y, 19, 3 ) ) newy
  6    from t
  7  /

         X Y                    NEWY
---------- -------------------- ------------------------------
         1 testing123fortom     tes tin g12 3fo rto m



is one approach, just substr over and over up to the length of your string and trim.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Krishna, May 06, 2009 - 10:47 am UTC

Thank you Tom.

Another use for stragg

Steve, May 08, 2009 - 7:29 am UTC

How about using 'stragg' - either modified so the seperator is a space, or unmodified if you know the input string is never going to contain a comma, use replace to swap it for a space:

select replace(stragg(substr('testing123forste',3*(level-1)+1,3)),',',' ')
from dual
connect by level <= ceil(length('testing123forste')/3)

simple and very useful

Hardik, June 10, 2014 - 11:19 am UTC

Hi Tom,
Thanks for this. It was very elegantly explained with a simple example.
Thanks a lot.

A reader, August 16, 2014 - 10:05 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.