sql – Find string with 2 constant digits and 8 more digits to follow

Question:

I need a text string to filter those that contain the following format:

   SELECT Referencia FROM A 
   WHERE Referencia LIKE '%[78][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

Where 78 is a fixed number and I need exactly 8 numeric characters to follow it.

String Example:

PLANO-7800002154_asdvd0.pdf

TOMO_1800015711.pdf

2016032213_Campo_AS.pdf

I just want the one with 78XXXXXXXX back

And then get just that string of numbers.

Answer:

The like should be for '%78[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' (the 78 without the straight parentheses).

To verify it with the examples that appear in the question, this query:

select val
from (
    select 'PLANO-7800002154_asdvd0.pdf' as val
    union
    select 'TOMO_1800015711.pdf'
    union
    select '2016032213_Campo_AS.pdf'
    ) as T
where val like '%78[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

returns only the searched record.

The query with the like as it was originally:

select val
from (
    select 'PLANO-7800002154_asdvd0.pdf' as val
    union
    select 'TOMO_1800015711.pdf'
    union
    select '2016032213_Campo_AS.pdf'
    ) as T
where val like '%[78][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

also returns the value TOMO_1800015711.pdf

Scroll to Top