SQL query using LIKE problem

All topics about programming / development in T-SQL or other languages for SQL Server.
Post Reply
Badger1
Posts: 1
Joined: Sun Jun 23, 2019 7:31 am

Sun Jun 23, 2019 9:15 am

I have a column value that is stored like '123.456.789' however when I try to use the LIKE function I am not getting any results.

Code: Select all

SELECT Number  FROM Home WHERE Number = '123456789'
The Number has a value of '123.456.789' but if I replace the EQUALS with a LIKE it doesn't seem to work:

Code: Select all

SELECT Number  FROM Home WHERE Number LIKE '123456789%'
SELECT Number  FROM Home WHERE Number LIKE '%123456789'
SELECT Number  FROM Home WHERE Number LIKE '%123456789%'
All of the above queries return no data, please can somebody help?
Bob547
Posts: 12
Joined: Sat Jun 22, 2019 7:05 pm

Sun Jun 23, 2019 9:19 am

Hi,

You can achieve this by using REPLACE() to remove the dots (.).

For example:

Code: Select all

SELECT Number
FROM(
     VALUES 
            ('123.456.789')
    ) T(Number)
WHERE REPLACE(Number, '.', '') = '123456789';
You can change your query to use the REPLACE():

Code: Select all

SELECT number FROM Home WHERE REPLACE(Number, '.', '') = '123456789';
Post Reply

Social Media