I have a table named Test
Test
Name Value1(varchar) Value2(varchar)
A 1 9
B 10 19
C 20 39
D 40 45
... ... ...
Now how do I get Name from Test table when I send a value like '8' or '21'etc
Um, why are your columns VARCHAR?
DECLARE @n INT = 8;
SELECT Name FROM dbo.Test
WHERE @n BETWEEN CONVERT(INT, Value1) AND CONVERT(INT, Value2);
If you have other junk in these columns you may have to do something like this to prevent SQL Server from outsmarting you and trying to convert 'ascfdgt' to an integer:
DECLARE @n INT = 8;
SELECT Name FROM dbo.Test
WHERE ISNUMERIC(Value1) = 1
AND ISNUMERIC(Value2) = 1
AND @n BETWEEN CASE WHEN ISNUMERIC(Value1) = 1 THEN CONVERT(INT, Value1) END
AND CASE WHEN ISNUMERIC(Value2) = 1 THEN CONVERT(INT, Value2) END;
Now please, either fix the data type of this column, or store these numbers in a different column.
If you pass in A12 and need to find rows where Value1 is A010 and Value2 is A029 then it gets a little more complex:
DECLARE @n VARCHAR(12) = 'A12';
SELECT Name FROM dbo.Test
WHERE ISNUMERIC(SUBSTRING(Value1, 2, 255)) = 1
AND ISNUMERIC(SUBSTRING(Value2, 2, 255)) = 1
AND CONVERT(INT, SUBSTRING(@n, 2, 255))
BETWEEN CASE WHEN ISNUMERIC(SUBSTRING(Value1, 2, 255)) = 1
THEN CONVERT(INT, SUBSTRING(Value1, 2, 255)) END
AND CASE WHEN ISNUMERIC(SUBSTRING(Value2, 2, 255)) = 1
THEN CONVERT(INT, SUBSTRING(Value2, 2, 255)) END;
See how ugly that is? Can you please, please, please consider fixing this terrible design so you don't have to litter your codebase with spaghetti queries like this?
value1' has A010 and value2` has A029 and pass value like A12
A and the number are two separate pieces of data, store the A in one column and the numbers in other columns.