MYSQL REGEX in Select query

Question

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.

What does SELECT VERSION(); return?

Nae
– Nae

2019-07-16 21:42:13 +00:00
Commented Jul 16, 2019 at 21:42 — Nae
– Nae, Commented Jul 16, 2019 at 21:42
SELECT VERSION() = 8.0.16

Vedansh B
– Vedansh B

2019-07-16 21:50:41 +00:00
Commented Jul 16, 2019 at 21:50 — Vedansh B
– Vedansh B, Commented Jul 16, 2019 at 21:50

Rick James · Accepted Answer · 2019-07-16 23:31:12Z

2

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

edited Jul 16, 2019 at 23:31

Rick James

144k15 gold badges144 silver badges255 bronze badges

answered Jul 16, 2019 at 21:35

Ferhat BAŞ

7977 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vedansh B Over a year ago

This works Ferhat, what if i need the values before - like 130, 109,118,102,109,118,109,116,149,141,130,102

Ferhat BAŞ Over a year ago

I am not good the regular expresision but when i need regex I use it this site regex101.com and try it

Ferhat BAŞ Over a year ago

Just use this regex "(- [0-9]+)" finding others

Vedansh B Over a year ago

Got it , Thanks Ferhat

spencer7593 · Accepted Answer · 2019-07-16 21:09:48Z

0

MySQL REGEXP returns a boolean, not a string.

excerpt from MySQL Reference Manual:

Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.

Maybe we want to use MySQL REGEXP_REPLACE function ?

Reference:

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace

answered Jul 16, 2019 at 21:09

spencer7593

109k15 gold badges122 silver badges148 bronze badges

1 Comment

Vedansh B Over a year ago

Thanks Spencer, Tried but it is just removing the '-' sign select REGEXP_REPLACE('130 - 66||109,118 - 3||102 - 18||109,118 - 2||109,116,149 - 14||141 - 8||130 - 31||102 - 12', '-',''); Output - 130 66||109,118 3||102 18||109,118 2||109,116,149 14||141 8||130 31||102 12

Collectives™ on Stack Overflow

MYSQL REGEX in Select query

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related