Question 113054 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Strip all dashes in string for GUIDs only

I don’t want to replace ALL dashes in a string, but rather replace only those dashes that are part of a GUID/UUID.

Before the replace:

Mary-Anne Smith is a physics professor at the University of Illinois. Her dissertation was focused on the Higgs-Boson particle. Her professor ID is 01140384-5189-11ed-beb7-fa163e98fdf8. You can reach her at [email protected].

After the replace:

Mary-Anne Smith is a physics professor at the University of Illinois. Her dissertation was focused on the Higgs-Boson particle. Her professor ID is 01140384518911edbeb7fa163e98fdf8. You can reach her at [email protected].

Tags:

2

Answers


  1. 0

    If you’re using MySQL 8.x, you can use REGEXP_SUBSTR() to get the UUID from the string, remove the dashes, and replace it with that.

    SELECT REPLACE(
    column,
    REGEXP_SUBSTR(column, '[0-9a-f]+-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]+'),
    REPLACE(REGEXP_SUBSTR(column, '[0-9a-f]+-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]+'), '-', '')) AS column_modified
FROM yourTable

    Note that this will only replace the first UUID in the column. If you need to do more replacements, you could write a stored function that repeats the replacement in a loop.

    Login or Signup to reply.
  2. 0

    Try this using Regexp_Replace

    Select Regexp_Replace
('string', 
'[0-9]{8}-[0-9]{4}-[a-z0-9]{4}-[a- 
 z0-9]{12}', '-','') from table;

    If we go by the sematics of the data we can also split it on "id is"

    Eg

    Select 
 Replace(Substr(string 
  Instr(String, 
 'Id is' 
  )+1,Instr(String, 
 'Id is' 
  )+1+len(hexIdformat), 
  '-','')

    Something like this above.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search