How to find variable pattern in MySql with Regex?

Question

I am trying to pull a product code from a long set of string formatted like a URL address. The pattern is always 3 letters followed by 3 or 4 numbers (ex. ???### or ???####). I have tried using REGEXP and LIKE syntax, but my results are off for both/I am not sure which operators to use.

The first select statement is close to trimming the URL to show just the code, but oftentimes will show a random string of numbers it may find in the URL string.

The second select statement is more rudimentary, but I am unsure which operators to use.

Which would be the quickest solution?

SELECT columnName, SUBSTR(columnName, LOCATE(columnName REGEXP "[^=\-][a-zA-Z]{3}[\d]{3,4}", columnName), LENGTH(columnName) - LOCATE(columnName REGEXP "[^=\-][a-zA-Z]{3}[\d]{3,4}", REVERSE(columnName))) AS extractedData FROM tableName

SELECT columnName FROM tableName WHERE columnName LIKE '%___###%' OR columnName LIKE '%___####%' 
-- Will take a substring of this result as well

Example Data:

randomwebsite.com/3982356923abcd1ab?random_code=12480712_ABC_DEF_ANOTHER_CODE-xyz123&hello_world=us&etc_etc

In this case, the desired string is "xyz123" and the location of said pattern is variable based on each entry.

EDIT

SELECT column, LOCATE(column REGEXP "([a-zA-Z]{3}[0-9]{3,4}$)", column), SUBSTR(column, LOCATE(column REGEXP "([a-zA-Z]{3}[0-9]{3,4}$)", column), LENGTH(column) - LOCATE(column REGEXP "^.*[a-zA-Z]{3}[0-9]{3,4}", REVERSE(column))) AS extractData From mainTable

This expression is still not grabbing the right data, but I feel like it may get me closer.

https://stackoverflow.com/questions/16646686/mysql-regexp-vs-like — Ergest Basha, Oct 07 '21 at 08:07
Then try `REGEXP_SUBSTR(column, '(?<=[&?]random_code=[^]{0,256}-)[a-zA-Z]{3}[0-9]{3,4}(?![^])')` — Wiktor Stribiżew, Oct 07 '21 at 08:57

Revuimar · Answer 1 · 2021-10-07T09:53:13.180

0

I'd make use of capture groups: (?<=[=\-\\])([a-zA-Z]{3}[\d]{3,4})(?=[&]) I assume with [^=\-] you wanted to capture string with "-","\" or "=" in front but not include those chars in the result. To do that use "positive lookbehind" (?<=. I also added a lookahead (?= for "&". If you'd like to fidget more with regex I recommend RegExr

edited Oct 07 '21 at 09:53

answered Oct 07 '21 at 08:08

Revuimar

303
1
11

1

Will select starting part if the number of digits is more than 4... https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5e70332ec01e3cb1723957ea8f142bd0 – Akina Oct 07 '21 at 08:20
@Revuimar I do not need to capture any "-" or "=" characters; I was hoping it would help with testing purposes to see where my match began. – Gordon Oct 07 '21 at 08:47
@Akina in your 4th output example there are five digits, so no match should be detected I would hope – Gordon Oct 07 '21 at 08:48
@Gordon This is true, and this is the problem of the provided solution. I hope that the answer author will fix this issue. – Akina Oct 07 '21 at 08:57

score 0 · Answer 2 · answered Oct 07 '21 at 09:23

I suggest using

REGEXP_SUBSTR(column, '(?<=[&?]random_code=[^&#]{0,256}-)[a-zA-Z]{3}[0-9]{3,4}(?![^&#])')

Details:

(?<=[&?]random_code=[^&#]{0,256}-) - immediately on the left, there must be & or &, random_code=, and then zero to 256 chars other than & and # followed with a - char
[a-zA-Z]{3} - three ASCII letters
[0-9]{3,4} - three to four ASCII digits
(?![^&#]) - that are followed either with &, # or end of string.

See the online demo:

WITH cte AS ( SELECT 'randomwebsite.com/3982356923abcd1ab?random_code=12480712_ABC_DEF_ANOTHER_CODE-xyz123&hello_world=us&etc_etc' val
              UNION ALL
              SELECT 'randomwebsite.com/3982356923abcd1ab?random_code=12480712_ABC_DEF_ANOTHER_CODE-xyz4567&hello_world=us&etc_etc'
              UNION ALL
              SELECT 'randomwebsite.com/3982356923abcd1ab?random_code=12480712_ABC_DEF_ANOTHER_CODE-xyz89&hello_world=us&etc_etc'
              UNION ALL
              SELECT 'randomwebsite.com/3982356923abcd1ab?random_code=12480712_ABC_DEF_ANOTHER_CODE-xyz00000&hello_world=us&etc_etc'
              UNION ALL
              SELECT 'randomwebsite.com/3982356923abcd1ab?random_code=12480712_ABC_DEF_ANOTHER_CODE-aaaaa11111&hello_world=us&etc_etc')
SELECT REGEXP_SUBSTR(val,'(?<=[&?]random_code=[^&#]{0,256}-)[a-zA-Z]{3}[0-9]{3,4}(?![^&#])') output
FROM cte

Output:

How to find variable pattern in MySql with Regex?

2 Answers2