I need to select sequences of numbers in a field of a table column

Question

I need to select sequences of numbers in a field of a table column

Navigation

#1 by (1 votes)

2

I have a 'Text' column in my 'process' table. Text consists of a VARCHAR (220), in this text I need to identify a specific word pattern. In this case a sequence of numbers with a "-" to separate:

'1234-567' - There are 4 digits followed by '-' and soon after 3 Digits;

And before this number there is a word like 'ProNumber:'. So I tried to use the Substring and Locate function to trace and show the result.

SELECT substring(Texto, locate('ProNumber:', Texto) +11,8) como número FROM processo;

But unfortunately this number appears several times in the 'Text' column field, and my query only shows the first one. Is there any way to do a query that returns the various numbers that the field contains?

Edited:
I would like the result to be something like:
Process | ProNumber
0001 | 1234-567
0001 | 8945-567
0002 | 1258-567
0003 | 1454-547
0004 | 1548-987
0004 | 1234-567

mysql regex query select

asked by anonymous 05.04.2017 / 18:22

1 answer

How not to use so many Activities? Receiving Token via WebRequest?

score 1 · Accepted Answer

Try using this regex pattern to capture what you need.

([\n]|^)(?=ProNumber: (\d*-\d*)*)

Since you did not specify if the number of digits contained in the sequence "123-456" may vary, I left the regex delimited only by the sequence "ProNumber:" by putting a positivelookahead but I advise you to delimit by the number of characters before and after of "-" if they are fixed values, avoiding unwanted catches.