How to identify different patterns of numbers in a column?

Question

I am trying to write one single formula to identify all the patterns in a column/field. For example: Below are the five different patterns

AG 5643 895468 UWEB
7546 695321 IJJK
PE 45612384
8642567921
16724385

Formula for First pattern: Contains 4 numbers 6 numbers '*[0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9][0-9][0-9] *' This is not working. Can we specify the length? Something like this [0-9]{4} - 4 digit number? First pattern should pick second one also.

3rd one: first 2 characters are alphabets 8 or 10 digit numbers

4th one: 10 digit number

5th one 8 digit number

Thanks in advance!

Answer 1

If you're working in MySQL you can use regular expressions with the RLIKE filter operator.

For example, WHERE text RLIKE '[0-9]{8}' finds all the rows with any consecutive sequence of eight digits in them anywhere. (http://sqlfiddle.com/#!9/44996/1/0)

WHERE text RLIKE '^[0-9]{8}%' finds the rows consisting of nothing but an eight-digit sequence. (http://sqlfiddle.com/#!9/44996/2/0)

WHERE text RLIKE '^[0-9A-Z]{2} ' finds the rows starting with two letters or digits and then a space. (http://sqlfiddle.com/#!9/44996/3/0)

You get the idea. Regular expressions have a lot of power to them, generally beyond the scope of a SO answer to explain. Beware, though. This is a common saying: If you solve a problem with e regular expression, now you have two problems. You need to be careful with them.