Consider below table, I should be able to get result as "A" when input any value between XYZ-00000001 and XYZ-00000005 for eg: XYZ-00000003 or XYZ-00000004
similarly when input is given as XYZ-00000008 value "B" should be resulted.
-
Click on the Bold part of the text to see the the table.Krishna Teja– Krishna Teja2016-09-21 09:07:37 +00:00Commented Sep 21, 2016 at 9:07
Add a comment
|
3 Answers
SELECT [NAME] FROM YourTable WHERE YourValue BETWEEN Val1 AND Val2
3 Comments
Jamiec
Should probably make it clear that this works because of the fixed length of
Val1 & Val2. They alphanumerically sort correctly. As soon as that fixed length changes, this no longer works.
Cato
@Jamiec - are you really sure about what you said there? What other sorting rule might be used? It would work in the sense of what was asked, sorting order is string collation order, for which there are clear rules. Any other collation method would have to be explained. If the length was varied, then they WOULD still sort according to alphanumeric sorting rules.
Jamiec
Yes Im sure, this works by virtue of it being fixed width. As soon as you have an extra character in there, this breaks. It doesnt affect your answer (which is of course correct) but it may affect the OP in future if they rely on this answer and someone else changes the length of data in those columns.
You can separate columns in characters and numbers and then input the value for comparison. Below code will answer 'A' for the input number between 1 to 5.
SELECT NAME
FROM
(
select *, left(val1,3) VAL1_ALFA, left(val2,3) VAL2_ALFA,
cast(REPLACE( val1, SUBSTRING( val1, PATINDEX( '%[a-z]%', val1 ), 4 ),'') as int) val1_NUM,
cast(REPLACE( val2, SUBSTRING( val2, PATINDEX( '%[a-z]%', val2 ), 4 ),'') as int) val2_NUM from Your_Table
) A
WHERE
4 BETWEEN val1_NUM AND VAL2_NUM
Comments
Use SUBSTRING AND CHARINDEX:
DECLARE @tblTest AS Table
(
Name VARCHAR(50),
VAl1 VARCHAR(50),
VAl2 VARCHAR(50)
)
INSERT INTO @tblTest VALUES
('A','XYZ-00000001','XYZ-00000005'),
('B','XYZ-00000006','XYZ-00000012'),
('C','XYZ-00000013','XYZ-00000019'),
('D','XYZ-00000020','XYZ-00000025')
DECLARE @SerachText VARCHAR(50)='XYZ-00000021'
SELECT
*
FROM @tblTest
WHERE
SUBSTRING(@SerachText,CHARINDEX('-',@SerachText)+1,LEN(@SerachText)) >=SUBSTRING(VAl1,CHARINDEX('-',VAl1)+1,LEN(VAl1))
AND
SUBSTRING(@SerachText,CHARINDEX('-',@SerachText)+1,LEN(@SerachText)) <=SUBSTRING(VAl2,CHARINDEX('-',VAl2)+1,LEN(VAl2))
answered Sep 21, 2016 at 9:28
Sandip - Frontend Developer
14.7k5 gold badges38 silver badges62 bronze badges