Search code examples
mysqlregexsyntaxmysql-8.0

MYSQL REGEX in Select query

Need a Regex solution to select only a pattern of string from a column.

Data looks like this:

Column1
Data Type = String 

Data = 
"130 - 66||109,118 - 3||102 - 18||109,118 - 2||109,116,149 - 14||141 - 8||130 - 31||102 - 12"

Expected Result 

66, 3, 18, 2, 14, 8, 31, 12

Tried REgex - "\-(...*?)\W" but it doesn't work.

Solution

  • You can try this

    SELECT REPLACE(REGEXP_REPLACE('130 - 66||109,118 - 3||102 - 18||109,118 - 2||109,116,149 - 14||141 - 8||130 - 31||102 - 12',
                                     '(([,0-9]+) - )', ''),'||',',');

    and result

    66,3,18,2,14,8,31,12

    Referance