HomeUncategorized › Replacing a string within a column

Replacing a string within a column

@rnm1978 asked an interesting question over Twitter, along the lines of:

“My column has “YYYY / MM” in it – how can I amend the column so that the date is in “PMM-YY” format? eg. ‘Here is some text 2010 / 04 and more text’ becomes ‘Here is some text P04-10 and more text’.”

REGEXP_REPLACE to the rescue!

SQL> with mt as (select 'Thisismy string 2010 / 04 and there''s more over here' col1 from dual union all
  2              select 'Just plain vanilla string' col1 from dual union all
  3              select 'Have some 2009 / 02 more text here' col1 from dual)
  4  select col1, regexp_replace(col1, '([[:digit:]]{2})([[:digit:]]{2}) / ([[:digit:]]{2})',
  5                                    'P\3-\2') col1a
  6  from   mt;

COL1                                                    COL1A
------------------------------------------------------- --------------------------------------------------
Thisismy string 2010 / 04 and there's more over here    Thisismy string P04-10 and there's more over here
Just plain vanilla string                               Just plain vanilla string
Have some 2009 / 02 more text here                      Have some P02-09 more text here

1 Comments.[ Leave a comment ]

  1. post content not properly aligned

    Hey BOneist,

    THe text is going over the right side pane and kind of distorting the serenity… could you fix it?? 🙂

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>