Skip to Main Content
  • Questions

  • How to insert a character inbetween 2digit in a block of 4 digit

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 25, 2021 - 9:18 am UTC

Last updated: February 26, 2021 - 11:46 am UTC

Version: PLSQL developer

Viewed 1000+ times

You Asked

Hi Tom,

I would like to add a character after 2 digits in a block of 4 digit in PL/SQL. I have to update those records in the table(Input) with (Output)
Eg:
Input 1234abc5678
Output 12:34abc56:78.

Could you please help.

Thankyou

and Chris said...

Here's one way:

- Use regular expressions to find a set of two numbers followed by a set of two numbers

([0-9][0-9])([0-9][0-9])


Placing each pair in brackets enables you to:

- Use back-references to access these, separated by a colon

\1:\2


Giving:

with rws as (
  select '1234abc5678' str from dual
)
  select regexp_replace ( 
           str,
           '([0-9][0-9])([0-9][0-9])',
           '\1:\2'
         ) s
  from   rws;
  
S               
12:34abc56:78

Rating

  (1 rating)

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

Comments

A reader, February 26, 2021 - 12:51 pm UTC

Thank you for your reply.
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.