2

This is a fairly odd question... I believe it would involve some sort of regex with MySQL.

Say I have a query on a table as such below:

    >select description from foo;
 ----------------------------------
 |   description                  |
 ----------------------------------
  Kitten, 3 in., Lightsaber
  Mouse, 5.5 in., Luke Skywalker
  Dog, 9.000 in., Death Star

Is there a way to update foo such that the "inches" (in.) portions are to three decimal places.

i.e. After the update:

    >select description from foo;
 -----------------------------------
 |   description                    |
 -----------------------------------
  Kitten, 3.000 in., Lightsaber
  Mouse, 5.500 in., Luke Skywalker
  Dog, 9.000 in., Death Star

Any advice/guidance is greatly appreciated! Thanks.

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
jparanich
  • 8,372
  • 4
  • 26
  • 34
  • 6
    Boo to composite fields in a RDBMS. If these values were each in their own properly-typed columns, there would be no work at all here. – Joe Sep 29 '11 at 20:45
  • Why aren't you using the Decimal type and formatting the numbers in the application? Using strings to store numbers is something that should only be done when the numbers have no numerical significance (such as phone numbers.) Your approach is short sighted and will be a headache if you ever need to compare or sort those values. – mikerobi Sep 29 '11 at 20:51
  • I know, it's unfortunate. The number formatting has since been done in the application but this is old data where inconsistencies linger. The field is built-up internally in a program and stored that way... just trying to mitigate the damage now :( – jparanich Sep 29 '11 at 20:53
  • Why the down vote? Sure, bad methodology; but no doubt encountered a countless number of times. – jparanich Sep 29 '11 at 21:04
  • 2
    I'm thinking it is easiest to just dump the whole DB, use sed to fix the portion, and then re-wrap it all up in insert statements. – jparanich Sep 29 '11 at 21:11

1 Answers1

1

According to the documentation, that's not possible in a SELECT command.

REGEXP only tells you if it's a match or not; it does not provide match indexes or substitution.

You should export the data to a flat text file, treat it with your favorite Regex enable text editor and re-apply the data to a brand new data structure. Seriously, you have to change your DB design.

Update

After further investigation, it seems like my answer should be: it's not possible out of the box.

Check this topic: How to do a regular expression replace in MySQL? You can do that via SQL through UDF.

Community
  • 1
  • 1
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123