I have the following mysql regex:

REGEXP '-v[0-9]{1,2}

Which catches -v01 and -v1 and -v03 and -v97, I want to add a dot . at the end of it:
-v01. and -v1. and -v03. and -v97.

I tried \. but that did not seem to work - even though it should.

What am I missing?

CodePudding user response：

You can do it using this regex :

REGEXP '-v[0-9]{1,2}\\. ?'

\\.  will make sure at least one "." after 2 digits to be exist

CodePudding user response：

 I think there are two solutions, maybe more

The First is,

  Here is the code 
 "REGEXP_REPLACE(a,'(-v)([a-zA-Z0-9] )','\1\2.')

 With backreferences \1\2 or $1$2 
  

The Second is,

SELECT obs, REGEXP_REPLACE (obs, '$', '.') AS RESULT
FROM (SELECT 'asda -v01' AS obs FROM DUAL
    UNION ALL
    SELECT 'aaaa -v12' AS obs FROM DUAL
    UNION ALL
    SELECT '123 -v97' AS obs FROM DUAL
    UNION ALL
    SELECT 'v42 -x12' AS obs FROM DUAL
    UNION ALL
    SELECT '-v01' AS obs FROM DUAL
    UNION ALL
    SELECT '-v12' AS obs FROM DUAL
    UNION ALL
    SELECT '-v97' AS obs FROM DUAL)

The first code Regex101

The second code Regex101

CodePudding user response：

WITH cte AS (
  SELECT '-v01' val UNION ALL
  SELECT '-v12' UNION ALL
  SELECT '-v97' UNION ALL
  SELECT '-x12' UNION ALL
  SELECT '-v01.' UNION ALL
  SELECT '-v12.' UNION ALL
  SELECT '-v97.'
)
SELECT val, val REGEXP '-v[0-9]{1,2}\\.?' FROM cte

fiddle