Good friends, I want to know how to use like inside a variable to know if there are 6,7,8 numbers. Does anyone know what would be the correct syntax for this case?
@listaMenus = '4,5,6,7,8,9,10'
if (@listaMenus like '%8%' or '%6%')
begin
print 'no puedo continuar'
end
The closest thing to what you request is using the CHARINDEX function.
Using the same example you mentioned, we would have the following
DECLARE @listaMenus VARCHAR(50) = '4,5,6,7,8,9,10'
IF CHARINDEX('8', @listaMenus)>0 OR CHARINDEX('6', @listaMenus)>0
BEGIN
PRINT 'no puedo continuar'
END
An interesting way is to "expand" the list separated by commas, in a table of values:
DECLARE @ListaMenus VARCHAR(255)
SELECT @ListaMenus = '4,5,6,7,8,9,10'
SELECT Split.a.value('.', 'VARCHAR(100)') AS Valor
FROM (
SELECT CAST ('<M>' + REPLACE(@ListaMenus, ',', '</M><M>') + '</M>' AS XML) AS Valores
) AS A
CROSS APPLY Valores.nodes ('/M') AS Split(a);
Basically we convert @ListaMenus into a XML to then expand each element in a new row. The result would be something like this:
Valor
4
5
6
7
8
9
10
And now nothing prevents us from doing a IN on this table, for example:
DECLARE @Existe INT
SELECT TOP 1 @Existe = Split.a.value('.', 'VARCHAR(100)')
FROM (
SELECT CAST ('<M>' + REPLACE(@ListaMenus, ',', '</M><M>') + '</M>' AS XML) AS Valores
) AS A
CROSS APPLY Valores.nodes ('/M') AS Split(a)
WHERE CONVERT(INT,Split.a.value('.', 'VARCHAR(100)')) IN (6, 7, 8)
IF @Existe IS NOT NULL BEGIN
PRINT 'Existe!!!'
END
Important : This solution is valid only from SQL 2008