LIKE operator for sequence of Numbers

Question

I am trying to use wildcard expression to fetch data related to a sequence of numbers. Can I know how to use a series of numbers inside wildcard expression LIKE [0-10].

here is my query:

select grade from table where grade LIKE [1-12]?

output: is 1 and 2

I referred to t-SQL book and they talk about LIKE N[1-12]. What's the difference between LIKE [1-12] and N[1-12]?

I can use between 1 and 12 to fetch my data. But I am just curious how to use a wildcard for series of numbers with LIKE operator?

Answer 1

But if you already have a column with a sequence of numbers and don't know the size, what I've done was this function:

CREATE FUNCTION Keep_Only_Int (@X VARCHAR(MAX)) RETURNS BIGINT AS BEGIN
    IF @X IS NULL RETURN NULL
    DECLARE @T AS INT = LEN(@X), @I AS INT = 0, @J AS CHAR(1), @RET AS VARCHAR(50) = ''
    WHILE @I < @T BEGIN
        SET @I += 1
        SET @J = SUBSTRING(@X, @I, 1)
        IF ASCII(@J) BETWEEN 48 AND 57 --Numbers, is needed because ¹, ² and ³ are going to return true in the link
            SET @RET += @J
    END
    
    IF LEN(@RET) > 19 RETURN NULL --Bigger then bigint
    RETURN NULLIF(@RET, '')
END

An example of usage:

create table #a (content varchar(100))
insert #a values ('My number is 123, whatever')
insert #a values ('My number is 1234, whatever')
insert #a values ('My number is ¹²³4, whatever')                --> Special numbers
insert #a values ('My number is one, whatever')                 --> No number
insert #a values ('My number is 1234567890123456789, whatever')
insert #a values ('My number is 12345678901234567890, whatever')--> This is too big!

select * 
    , dbo.Keep_Only_Int(content)
from #a

The function already convert the field to BIGINT, so you can use an between statement

select * 
from #a
where dbo.Keep_Only_Int(content) between 1 and 2000

It is not focused on a great performance, if you are using a table too big I'd recomend creating a specific code for that