How to replace multiple matching patterns with multiple specified replacement strings in SQL - Oracle [duplicate]

Question

We have varchar2(250) column that have various text data we want to replace with other specific text. Note: I do not want to use multiple replace function for this requirement, is there is any better way to do it

requirement is to:

Replace <   with    "less than"
Replace >   With    "greater than"
Replace &   With    and

For example data in table is:

"amount must be < 1 & > 2"

Output of SQL should be:

"amount must be greater than 1 and less than 2"

What's the problem with REPLACE when it is what meant for this? — Lalit Kumar B
– Lalit Kumar B, Commented Feb 18, 2015 at 10:20
Yes, this is one column data, for solution please use dual and sample text above — Deepak Khatri
– Deepak Khatri, Commented Feb 18, 2015 at 10:21
The problem with this is we need to replace 15 to 20 characters with string value — Deepak Khatri
– Deepak Khatri, Commented Feb 18, 2015 at 10:28
"I do not want to use multiple replace function" - why? What is the real problem you are trying to solve here? — user330315
– user330315, Commented Feb 18, 2015 at 11:10

Ankit Bajpai · Accepted Answer · 2015-02-18 10:22:38Z

1

According to me you have to use 3 replacement statements. Somthing like this:-

SELECT REPLACE( REPLACE( REPLACE ('amount must be < 1 & > 2', '<', 'less than'), '>' ,'greater than'), '&', 'and')
FROM DUAL;

I think this is only the solution.

answered Feb 18, 2015 at 10:22

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

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

Comments

Lalit Kumar B · Accepted Answer · 2015-02-18 10:22:44Z

1

You just need to use REPLACE thrice, that isn't a lot of effort.

SQL> WITH DATA AS(
  2  SELECT 'amount must be < 1 & > 2' str FROM dual
  3  )
  4  SELECT REPLACE(REPLACE(REPLACE(str, '<', 'less than'), '&','and'),'>','greater than') str
  5  FROM DATA
  6  /

STR
---------------------------------------------
amount must be less than 1 and greater than 2

SQL>

answered Feb 18, 2015 at 10:22

Lalit Kumar B

49.5k13 gold badges105 silver badges132 bronze badges

Comments

Pரதீப் · Accepted Answer · 2015-02-18 10:29:48Z

1

Use replace function

SELECT Replace(
          Replace(
            Replace(a, '>', 'greater than'), 
                              '<', 'lesser than'),
                                           '&', 'and')
FROM   Yourtable

DEMO

edited Feb 18, 2015 at 10:29

answered Feb 18, 2015 at 10:23

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

How to replace multiple matching patterns with multiple specified replacement strings in SQL - Oracle [duplicate]

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related