SQL Like/Contains on BIGINT Column

Question

I have a BIGINT column that I want to do a partial match on.

e.g. @search = 1 should return all records where the first number is 1 (1, 11, 100 etc). Basically the same as a varchar LIKE.

I have tried:

DECLARE @search VARCHAR
SET @search = '1'

and

SET @search = '1%'

And used:

SELECT
    id FROM table
WHERE
    CAST(id AS varchar) LIKE @search

Adding a % to @search doesn't help. Any ideas how to accomplish this?

EDIT: it seems to be the variable. If I hard code the string in the WHERE clause I get the results I am looking for.

SELECT id FROM table WHERE CAST(id AS VARCHAR) LIKE '14%'

This gives me all records with an id of 14* (14, 140, 1400 etc).

Answer 1

Try this instead:

DECLARE @search VARCHAR(10)
SET @search = '1'

SELECT id FROM table WHERE CAST(id AS VARCHAR(10)) LIKE @search + '%'

When casting to VARCHAR, you should always specify the length. If you don't define a length, SQL-Server will assign one for you. Sometimes it will be 1 others it will be 30. Read this for more information.

Answer 2

Not a like but should do it. Replace x with id.

WHERE
(x >= 1000000000000000000 AND x < 2000000000000000000) OR
(x >= 100000000000000000 AND x < 200000000000000000) OR
(x >= 10000000000000000 AND x < 20000000000000000) OR
(x >= 1000000000000000 AND x < 2000000000000000) OR
(x >= 100000000000000 AND x < 200000000000000) OR
(x >= 10000000000000 AND x < 20000000000000) OR
(x >= 1000000000000 AND x < 2000000000000) OR
(x >= 100000000000 AND x < 200000000000) OR
(x >= 10000000000 AND x < 20000000000) OR
(x >= 1000000000 AND x < 2000000000) OR
(x >= 100000000 AND x < 200000000) OR
(x >= 10000000 AND x < 20000000) OR
(x >= 1000000 AND x < 2000000) OR
(x >= 100000 AND x < 200000) OR
(x >= 10000 AND x < 20000) OR
(x >= 1000 AND x < 2000) OR
(x >= 100 AND x < 200) OR
(x >= 10 AND x < 20) OR
(x = 1)

Answer 3

Instead of using the LIKE operator try using the LEFT function. This will return the left part of a character string with the specified number of characters.

SELECT id 
FROM table
WHERE LEFT(CAST(id AS varchar), 1) = '1'

I'm not certain, but I've got to assume this is will have better performance than using the LIKE operator, especially since you know you just want to compare to the beginning characters. Often using a function in the WHERE clause can often cause poor performance because the query can't take advantage of any indexes that might exist on the column. However in this case the query calling the CAST function, so the benefit of the index is already lost.

Edit: If the comparison needs to be for a variable number of digits, then you can use the LEN function to determine the number of characters for the LEFT function to return.

SELECT id 
FROM table
WHERE LEFT(CAST(id AS varchar),LEN(@search)) = @search

Answer 4

Problem is with your @search. Try like below instead

create table aaa (id bigint);
insert into aaa values(1),(11),(100),(111),(1111),(2222);

Select Query

SELECT
    id FROM aaa
WHERE
    CAST(id AS varchar(1000)) LIKE '1%';

(OR) like below

DECLARE @search VARCHAR;
SET @search = '1';

SELECT
    id FROM aaa
WHERE
    CAST(id AS varchar(1000)) LIKE @search + '%';