Update a string using regular expressions in SQL

Question

I have a string in my certain columns of my database:

<img title="\frac{3}{8}" src="http://latex.codecogs.com/gif.latex?\dpi{50}&amp;space;\fn_phv&amp;space;\frac{3}{8}" alt="" />

Basically it contains a html code for a fraction. But now I would like to replace it with:

<sup>3</sup>&frasl;<sub>8</sub>

I know I can update the value in the database as such:

UPDATE table 
SET `field` = Replace(`option`, '<img title="\frac{3}{8}" src="http://latex.codecogs.com/gif.latex?\dpi{50}&amp;space;\fn_phv&amp;space;\frac{3}{8}" alt="" />','<sup>3</sup>&frasl;<sub>8</sub>')
WHERE `filed` LIKE  '%<img title="\frac{3}{8}" src="http://latex.codecogs.com/gif.latex?\dpi{50}&amp;space;\fn_phv&amp;space;\frac{3}{8}" alt="" />%'

However, the word "\frac{3}{8}" can change accordingly. The number in the parentheses can change and when it changes I need to change the html tag that will replace to change accordingly as well. I know I need to use regular expressions, but not sure how to do it in SQL.

Need some guidance to do it.

It would have to assess whether it is useful to apply the replace_sections function of the common_schema framework. — wchiquito
– wchiquito, Commented Jun 19, 2015 at 14:26

Rick James · Accepted Answer · 2015-06-20 00:57:58Z

1

Why update the table? Why even have this stored in a table?

It seems like your application code (PHP?) could construct the string:

$numerator = 3;
$denominator = 8;
$img = "<img
      title="\frac{$numerator}{$denominator}"
      src=\"http://latex.codecogs.com/gif.latex?\dpi{50}&amp;
            space;\fn_phv&amp;space;\frac{$numerator}{$denominator}\"
      alt=''
      />";

(etc)

There is no REGEXP_REPLACE, except in MariaDB.

answered Jun 20, 2015 at 0:57

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

Comments

spencer7593 · Accepted Answer · 2015-06-19 13:49:03Z

0

In MySQL, regular expressions are only supported for finding "matches", the REGEXP operator, which returns a boolean. There is no support for using regular expressions to return another value.

Reference: 12.5.2 Regular Expressions https://dev.mysql.com/doc/refman/5.6/en/regexp.html

You said that you "need to use regular expressions" to do string manipulation. That means you will have to extract the column values from the database into a client application, and perform the string manipulation in the client app. (From the client, issue an UPDATE statement to assign a new value to the column, replacing the existing value.)

EDIT

If you absolutely had to do this in a SQL statement, then one option is to create a user defined function that could be used to perform the string manipulation.

https://dev.mysql.com/doc/refman/5.6/en/adding-functions.html

It's possible someone has already coded one that does something similar to what you need. I don't want to give the impression that user-defined functions are a silver bullet; I mention it because it is an option.

edited Jun 19, 2015 at 13:49

answered Jun 19, 2015 at 13:41

spencer7593

109k15 gold badges122 silver badges148 bronze badges

1 Comment

lakshmen Over a year ago

Can i do it without regular expressions?

Collectives™ on Stack Overflow

Update a string using regular expressions in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related