LIKE с целыми числами, в SQL

Ответ 1

select * from FOOS where FOOID like 2

следует избегать, так как это приведет к тому, что обе стороны будут неявно отображаться как varchar и означают, что индекс не может использоваться для удовлетворения запроса.

CREATE  TABLE #FOOS
(
FOOID INT PRIMARY KEY,
Filler CHAR(1000)
)
INSERT INTO #FOOS(FOOID)
SELECT DISTINCT number 
FROM master..spt_values


SELECT * FROM #FOOS WHERE FOOID LIKE 2

SELECT * FROM #FOOS WHERE FOOID = 2

DROP TABLE #FOOS

Планы (обратите внимание на сметные расходы)

Еще один способ увидеть разницу в затратах - добавить SET STATISTICS IO ON

Вы видите, что первая версия возвращает что-то вроде

Table '#FOOS__000000000015'. Scan count 1, logical reads 310

Вторая версия возвращает

Table '#FOOS__000000000015'. Scan count 0, logical reads 2

Это означает, что считывания, необходимые для поиска по этому индексу, пропорциональны глубине индекса, тогда как считывания, необходимые для сканирования, пропорциональны количеству страниц в индексе. Чем больше столбик, тем больше будет расхождение между этими двумя числами. Вы можете увидеть обе эти цифры, выполнив следующее.

SELECT index_depth, page_count
FROM
sys.dm_db_index_physical_stats (2,object_id('tempdb..#FOOS'), DEFAULT,DEFAULT, DEFAULT)
WHERE object_id = object_id('tempdb..#FOOS') /*In case it hasn't been created yet*/

Ответ 2

Используйте инструкцию CASE для преобразования входной строки в целое число. Преобразуйте подстановочный знак % в NULL. Это даст лучшую производительность, чем неявное преобразование всего столбца int в строку.

CREATE PROCEDURE GetFoos(@fooIdOrWildcard varchar(100))
AS
BEGIN
    DECLARE @fooId int

    SET @fooId =
        CASE
            -- Case 1 - Wildcard 
            WHEN @fooIdOrWildcard = '%'
                THEN NULL
            -- Case 2 - Integer
            WHEN LEN(@fooIdOrWildcard) BETWEEN 1 AND 9
            AND @fooIdOrWildcard NOT LIKE '%[^0-9]%'
                THEN CAST(@fooIdOrWildcard AS int)
            -- Case 3 - Invalid input
            ELSE 0
        END

    SELECT FooId, Name
    FROM dbo.Foos
    WHERE FooId BETWEEN COALESCE(@fooId, 1) AND COALESCE(@fooId, 2147483647)
END

Ответ 3

Да, вы можете просто использовать его:

SELECT  *
FROM    FOOS
WHERE   FOOID like 2   

или

SELECT  *
FROM    FOOS
WHERE   FOOID like '%'  

Целые числа будут неявно преобразованы в строки.

Обратите внимание, что ни одно из этих условий не поддается, i. е. способный использовать индекс на fooid. Это всегда приведет к полному сканированию таблицы (или просмотру полного индекса на fooid).

Ответ 4

Это поздний комментарий, но я подумал, что, может быть, некоторые другие люди ищут одно и то же, так как я смог найти решение для этого, я думал, что должен поделиться им здесь:)

Краткое описание проблемы: проблема, которую я имел, заключалась в том, чтобы использовать типы данных с целыми числами wild card foe. Я использую SQL Server, поэтому мой синтаксис предназначен для SQL Server. У меня есть столбец, который показывает номер отдела, и я хотел передать переменную с моей страницы из выпадающего меню. Существует опция "Все", которая в этом случае я хотел передать "%" в качестве параметра. Я использовал это:

select * from table1 where deptNo Like @DepartmentID

Он работал, когда я передавал число, но не для %, потому что sql-сервер неявно преобразует @DepartmentID в int (поскольку мой deptNo имеет тип int)

Итак, я нажал deptNo и исправил проблему:

select * from table1 where CAST(deptNo AS varchar(2)) Like @DepartmentID

Этот работает для обоих, когда я передаю число, подобное 4, и когда я передаю %.

Ответ 5

Используйте NULL как значение параметра вместо% для вашего шаблона

select * from table1 where (@DepartmentID IS NULL OR deptNo = @DepartmentID)