Using between clause on nvarchar column to find range is not working

Question

I have 3 nvarchar columns user_3, user_4 and description. I am setting yes and no flag. If the value in description column is equal or in between user_3 and User4 then set the flag to 'N' else set the flag to 'Y'.

Here is the SQL script I wrote so far. it works in some instances but not always. See the image with results. it worked on line #1 but didn't work on line # 6 for example. What am I doing wrong?

SELECT  [B].USER_3,[B].USER_4,A.DESCRIPTION,
(case when Isnumeric(A.DESCRIPTION) <> 1 then 'Y' 
else case when (CASE WHEN Isnumeric(A.DESCRIPTION) = 1 then
cast(A.DESCRIPTION AS decimal(10,5)) else 0 end) 
between ( CASE WHEN Isnumeric([B].USER_4) = 1 then 
cast([B].USER_4 AS decimal(10,5)) else 0 end) and
(CASE WHEN Isnumeric([B].USER_3) = 1 then cast([B].USER_3  AS decimal(10,5)) else 0 end)
then 'N' else 'Y' end end) as Flagset
from A , B

Here is the screenshot of the results

enter image description here

Answer 1

The issue is with your use of BETWEEN as per the docs:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

Because you don't know whether USER_3 or USER_4 is the higher limit or the lower limit, you need to test both ways.

Note: For this sort of query I prefer to pre-calculate all the values (using CROSS APPLY in this case) I need. It makes it much easier to follow and debug.

SELECT USER_3, USER_4, [DESCRIPTION]

    , CASE WHEN ISNUMERIC([DESCRIPTION]) <> 1 THEN 'Y' ELSE
        CASE WHEN CASE WHEN ISNUMERIC([DESCRIPTION]) = 1 THEN CAST([DESCRIPTION] AS decimal(10,5)) ELSE 0 END BETWEEN CASE WHEN ISNUMERIC(USER_4) = 1 THEN CAST(USER_4 AS decimal(10,5)) ELSE 0 END AND
        CASE WHEN ISNUMERIC(USER_3) = 1 THEN CAST(USER_3  AS decimal(10,5)) ELSE 0 END
    THEN 'N' ELSE 'Y' END END AS Flagset

    , CASE WHEN DNUMERIC <> 1 THEN 'Y' ELSE CASE WHEN DESCRIPTIOND BETWEEN USER_4D AND USER_3D OR DESCRIPTIOND BETWEEN USER_3D AND USER_4D THEN 'N' ELSE 'Y' END END CorrectedFlagSet

FROM (VALUES
    ('1.395','1.385','1.390')
    , ('22.025','41.425','22')
    , ('22.025','41.425','23.025')
) AS X (USER_3, USER_4, [DESCRIPTION])
CROSS APPLY (VALUES (
    CASE WHEN ISNUMERIC(USER_3) = 1 THEN CAST(USER_3 AS decimal(10,5)) ELSE 0 END
    , CASE WHEN ISNUMERIC(USER_4) = 1 THEN CAST(USER_4 AS decimal(10,5)) ELSE 0 END
    , CASE WHEN ISNUMERIC([DESCRIPTION]) = 1 THEN CAST([DESCRIPTION] AS decimal(10,5)) ELSE 0 END
    , CASE WHEN ISNUMERIC([DESCRIPTION]) = 1 THEN 1 ELSE 0 END
)) AS Y (USER_3D, USER_4D, DESCRIPTIOND, DNUMERIC);

Returns:

USER_3	USER_4	DESCRIPTION	Flagset	CorrectedFlagSet
1.395	1.385	1.390	N	N
22.025	41.425	22	Y	Y
22.025	41.425	23.025	Y	N

I'm sure I don't have to mention that you should really be storing this data in numeric form in the first place as it will perform better and save you lots of future issues.

And well laid out queries with consistent casing also helping understand and debug them.

Finally providing a minimal reproducible example with sample data, your query and your desired result as shown here makes it much easier for people to assist.