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

Add a dot to a mysql regex

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.
so that it only catches version number followed by the dot.

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

What am I missing?

Tags:

4

Answers


  1. 0 
    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
    val val REGEXP ‘-v[0-9]{1,2}.?’
    -v01 1
    -v12 1
    -v97 1
    -x12 0
    -v01. 1
    -v12. 1
    -v97. 1

    fiddle

    Login or Signup to reply.
  2. 0

    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

    This working for mysql 8.

    on older mysql you can’t use +? and *? It throw repetition-operator operand invalid

    Login or Signup to reply.
  3. 0 
     I think there are two solutions, maybe more

    The First is,

      Here is the code 
 "REGEXP_REPLACE(a,'(-v)([a-zA-Z0-9]+)','12.')

 With backreferences 12 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

    Login or Signup to reply.
  4. 0

    This is a working solution for all mysql versions.

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

\.{1,} instead of \.+ will work fine with no *repetition-operator operand invalid* error.
It will make sure at least one "." after 2 digits to be exist

    Seems you are working with an older version of mysql.

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

Back To Top
Search