Skip to Main Content
  • Questions

  • adding space between a number and a letter in a record

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vinay.

Asked: March 06, 2018 - 9:21 pm UTC

Last updated: March 08, 2018 - 2:59 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I want to add space between number and a letter in a column.
For eg: somestreet 22a --> Somestreet 22 A
oldstreet 5d --> Oldstreet 5 D
othernewtreet 134B --> Othernewstreet 134 B

Similarly, for all such records in a column.

Can you please help me with this?

Thanks in advance:-)

and Chris said...

A little regular expression will do the trick:

with rws as (
  select 'somestreet 22a' str from dual union all
  select 'oldstreet 5d' str from dual union all
  select 'othernewtreet 134B' from dual
)
  select regexp_replace(str, '([[:digit:]]+)([[:alpha:]]+)', '\1 \2') new_str
  from   rws;

NEW_STR               
somestreet 22 a       
oldstreet 5 d         
othernewtreet 134 B


Which means:

- ([[:digit:]]+)([[:alpha:]]+) => Search for one or more digits, followed by one or more letters
- \1 \2 => Return the first group (the numbers). Followed by a space. Followed by the second group (the letters)

Rating

  (1 rating)

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

Comments

Perfect solution!!

Vinay, March 07, 2018 - 7:02 pm UTC

Hi Chris,

This is the exact solution for the problem.

Thanks for the help.

Best Regards,
Vinay
Connor McDonald
March 08, 2018 - 2:59 am UTC

glad we could help
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.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.