using regexp_replace to transform numbers into strings

Question

I'm trying to use regexp_replace replace every occurrence of type (number) (brackets with a number inside) with the expression ('number') (add ' before and after the number and by that - transform the number into varchar).

Example -

Before change: abcde(737)sbsgs37(6)s(v)

After change : abcde('737')sbsgs37('6')s(v)

Thanks,

Ilya Golosovsky.

Sample data and desired results would really help explain what you are trying to do. — Gordon Linoff
– Gordon Linoff, Commented Nov 10, 2015 at 12:12
The input data is a VARCHAR2 containing alphabetical characters, punctuation and numbers - how will wrapping the numbers in quotes within a VARCHAR2 type magically change parts of the string to a NUMBER type such that wrapping them in quotes will make any difference? — MT0
– MT0, Commented Nov 10, 2015 at 12:37

Wernfried Domscheit · Accepted Answer · 2015-11-10 13:41:02Z

2

You can use this one

regexp_replace('abcde(737)sbsgs37(6)s(v)', '\((\d+)\)', '(''\1''))

-> abcde('737')sbsgs37('6')s(v)

DEMO

edited Nov 10, 2015 at 13:41

answered Nov 10, 2015 at 12:19

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

golosovsky Over a year ago

Hi, thanks for the answer but this doesn't suite my case. You're targeting any number, I just need the want the ones in brackets.

Wernfried Domscheit Over a year ago

I changed it (using less brackets than @pablomatico)

pablomatico · Accepted Answer · 2015-11-10 15:09:47Z

1

All credit for Wernfried Domscheit as he just missed one little thing. You could use this one:

regexp_replace('abcde(737)sbsgs37(6)s(v)', '(\()(\d+)(\))', '\1''\2''\3')

Tested and worked:

select regexp_replace('abcde(737)sbsgs37(6)s(v)', '(\()(\d+)(\))', '\1''\2''\3') from dual

edited Nov 10, 2015 at 15:09

answered Nov 10, 2015 at 12:58

pablomatico

2,24221 silver badges25 bronze badges

2 Comments

Gary_W Over a year ago

Since you've already captured the parens, you could use those in the output instead of hardcoding more: select regexp_replace('abcde(737)sbsgs37(6)s(v)', '(\()(\d+)(\))', '\1''\2''\3') from dual;.

pablomatico Over a year ago

Thanks @Gary_W! You're right I just edited the answer.

Collectives™ on Stack Overflow

using regexp_replace to transform numbers into strings

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related