CODEWITHSUNDEEP
sqlregexoracle-database
Zesty
Zesty

Reputation: 3001

How can I replace all values in this string?

I've got a string in a column in a table with different "size" values. E.g.:

select 'Test size="7" size="14" text size="14" line="22" other size="10"  size="9" '
from dual;

How can I change all values of size in the string without replacing other numbers in the string?

The value to replace will be the same for all instances. E.g. replace to 8:

select 'Test size="8" size="8" text size="8" line="22" other size="8"  size="8" '
from dual;

I tried, but it's not taking effect. Any ideas?:

select regexp_replace('Test size="7" size="14" text size="14" line="22" other size="10"  size="9" ', '/size="[0-9]+"/g', 'size="8"')
from dual

Upvotes: 0

Views: 62

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

SQL> WITH DATA AS
  2    (SELECT 'Test size="7" size="14" text size="14" line="22" other size="10"  size="9" ' STR
  3    FROM DUAL
  4    )
  5  SELECT REGEXP_REPLACE(STR, 'size="[[:digit:]]+"','size="8"') STR
  6  FROM DATA
  7  /

STR
------------------------------------------------------------------------
Test size="8" size="8" text size="8" line="22" other size="8"  size="8"

SQL>

Upvotes: 3

Ben
Ben

Reputation: 52923

If you want to change all occurrences to the same number then it would be simplest to use REGEXP_REPLACE() with the appropriate arguments. The 4th parameter is the occurrence of the string you want to replace; a 0 means all occurrences. The reason why the regular expression you've just added isn't working is because you haven't used this parameter.

with s as (
select 'Test size="7" size="14" text size="14" line="22" other size="10"  size="9" ' as str
  from dual
       )
select regexp_replace(str, 'size="\d+"', 'size="8"', 1, 0)
  from s

The regular expression looks for the string size=", followed by one or more digits \d+ followed by a double quote ". It then replaces this string with one that has the new number in.

Upvotes: 4

Related Questions