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

Asked

Viewed 504 times

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' - Are 4 digits followed by '-' and then after 3 digits;

And before this number is a word like 'Pronumber: '. So I tried to use the Substring and Locate function, to track 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 make a query that returns the various numbers that the field contains?

Edited:
I wish the result was something like:
Process | Pronumber
   0001 | 1234-567
   0001 | 8945-567
   0002 | 1258-567
   0003 | 1454-547
   0004 | 1548-987
   0004 | 1234-567

mysql database regex query select

  • Your question is a little confused. You want to make a filter (WHERE) that finds records with this numeric pattern or wants to have an output on SELECT that removes from the text some characters that are not in this pattern?

  • I’m sorry, what I’m wanting is for the result of Select to be a column, in show the sequences of numbers, contained in the field. In the middle of the field there may be for example 3 of these sequences, I would like the 3 to be returned by Select.

  • 1

    Hello, please add some example records and the exact output (SELECT result) you need?

1 answer

1


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" can vary left the regex delimited only by the sequence "Pronumber: " placing a positivelookahead but I advise you to delimit by the number of characters before and after "-" if they are fixed values, avoiding unwanted catches.

  • Yes, they are fixed values! Both before "-" and after. I would need these values to be delivered by Select itself. For each Process that all numbers contained in the Text field are displayed.

Browser other questions tagged mysql database regex query select

You are not signed in. Login or sign up in order to post.