Query SQL with similar values

I have to make a query to a base using as a comparison a string like this 12345678, but the value to compare is this way12.345.678, if I do the following query it does not return anything.

SELECT * FROM TABLA WHERE CAMPO = '12345678'

Where CAMPO would have the value of (12.345.678), if I replace = with a like, it does not return the data either

SELECT * FROM TABLA WHERE CAMPO like '12345678%'
SELECT * FROM TABLA WHERE CAMPO like '%12345678'
SELECT * FROM TABLA WHERE CAMPO like '%12345678%'

None of the 3 previous consultations works for me, how can I make this query?

The value can be of either 7, 8 or 9 numbers and the. It has to be every 3 from the end to the beginning

Answer

Use REPLACE() function to replace all the dots '.' as

SELECT *
FROM(
     VALUES ('12.345.678'),
            ('23.456.789')
    ) T(CAMPO)
WHERE REPLACE(CAMPO, '.', '') = '12345678';

Your query should be

SELECT * FROM TABLA WHERE REPLACE(CAMPO, '.', '') = '12345678';

Advertisement

Answer