Strip all dashes in string for GUIDs only-CodePudding

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].

CodePudding user response：

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.

CodePudding user response：

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"

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

Something like this above.