CODEWITHSUNDEEP
sql
user3594100
user3594100

Reputation: 15

Select column modification

Say we have this value in a column

<color="blue" size="5"><color="red">

How do I replace the "red" with blue using a SQL statement?

(the value dynamic so using regular REPLACE won't do)

I'm really puzzled by how to solve it.

Upvotes: 0

Views: 69

Answers (3)

ob1quixote
ob1quixote

Reputation: 399

REGEXP_REPLACE uses regular expressions to allow you to specify a pattern you wish you replace in a given string. Since your two color tags differ in structure, you can take advantage of this to specify a pattern which matches the second instance but not the first like so (SQL Fiddle)

SELECT your_column,
       REGEXP_REPLACE(your_column, '(.*)<color=".+">', '\1<color="blue">') AS "your_column_fixed"
  FROM your_table
;

This will take an input of the form <color="blue" size="5"><color="???"> and transform it to one with the second tag replaced with <color="blue">. The . operator in regular expressions matches any non-null character. The + modifier means to match one or more occurrences of .. Surrounding an operator with parentheses means that the value matched will be available in the replace argument as \n where n is the corresponding position in the pattern.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Here is a brute force expression:

select substr(col, 1, instr(col, '><color="'))||'<color="blue">'

It finds the first occurrence of '><color=' and replaces the string from that point on with '<color="blue">'.

Upvotes: 0

user3594597
user3594597

Reputation: 1

I would play around with substrings and patindex. 

DECLARE @COLOR varchar(20),
@YOUR_VALUE varchar(100) 

SET @YOUR_VALUE = SUBSTRING(@YOUR_VALUE, 8, LEN(@YOUR_VALUE))
SET @COLOR = SUBSTRING(@YOUR_VALUE, 0, PATINDEX('%"%', @YOUR_VALUE))

The above removes 'color=" which will always be length 8, then finds the index of the next '"', then sets @COLOR to a substring up to that index.

Upvotes: 0

Related Questions