Regex exact match of numbers: Oracle SQL developer

Question

I have a column filled with numbers and text. I want to find an exact combination of numbers i.e. 231

For e.g.

Not Ideal: Hello, the number is 231 but that's not what we want.
Ideal: Hello, the number is 231-12342. Ah this is what I want
Ideal: Hello, the number is 231 0000000. Ah this also I want.
Ideal: Hello, the number is (231)-0000000 also of interest.
Not Ideal: Hello, the number is 00023100000 is not what we want

Ideal output: Text 2,3,and 4.

Good Example:

Bad:

Text1 is also a solution to your problem. Text5 too ! English is not my natural language, but i think that writing 'which might be' is incorrect. Using 'must be' is certainly better. Can you please improve your sentence beginning with 'Ideally' ? Thanks — schlebe, Jul 09 '20 at 20:28

score 0 · Accepted Answer · answered Jul 09 '20 at 20:25

0

You don't need regular expressions.

select *
from your_table
where instr(text_column, '231') > 0

But, then again, all examples you posted match. What's wrong with Text1's 231? It has whitespaces arround it. Or Text5, which has "other characters" that surround it.

answered Jul 09 '20 at 20:25

Littlefoot

131,892
15
35
57

I do, because I am only looking for 231 and not 456**231**90. In the above instance both are a match. – Data Monger Jul 09 '20 at 20:30
Sorry, but that's not what you said: "might be proceeded or succeeded by whitespace, and or any other characters." – Littlefoot Jul 09 '20 at 20:39
That was an error on my part. I do apologize. – Data Monger Jul 09 '20 at 20:41
231-XXXXX Lorem Ipsum is simply dummy text of the printing and typesetting industry. Good 000-000-2310 Lorem Ipsum is simply dummy text of the printing and typesetting industry. Bad – Data Monger Jul 09 '20 at 20:42

Regex exact match of numbers: Oracle SQL developer

1 Answers1