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