Between statement not working correctly
-
I need to get values in between 4294967296 and 8589934591, So in SQL, i am using where column1 between 8589934591 and 8589934591. But in the result values like 7220817 is also returning. It looks like SQL is adding trailing zeros. All columns are varchar datatype and all columns are alplanumeric.
Query&Result
-
Is DecoderCode always a number? If so, store it in a column with a numeric data type. If you don't have any control over the database design, convert it to (big)int in your query.
John
- pavandpavan - Friday, June 2, 2017 7:04 AMI need to get values in between 4294967296 and 8589934591, So in SQL, i am using where column1 between 8589934591 and 8589934591. But in the result values like 7220817 is also returning. It looks like SQL is adding trailing zeros. All columns are varchar datatype and all columns are alplanumeric.
Query&ResultCan you post the actual execution plan and the table DDL please?
😎 -
To just add to what others have said, the BETWEEN is working correctly.
You're storing these values as strings, not numbers.
It's not that trailing zeros are being added; it's that this is how string comparison works.
It's no different than realizing that 'bbbbbbbbb' is between 'a' and 'c', but a bit easier to misinterpret because your string looks like a number.
Cheers!
-
@john-2 Mitchell-245523 - DecoderCode is alpanumeric .
@Jacob Wilkins:-DecoderCode,FromSerialNo and ToSerialNo columns have aplanumeric data. any suggestions regarding solution? - pavandpavan - Friday, June 2, 2017 9:25 AM
Without knowing the specific logic you want to implement, it's difficult to say for sure.
One potential approach would be to also require that LEN(DecoderCode) is BETWEEN LEN (FromSerialNo) and LEN (ToSerialNo).
That would prevent undesired matches like the one you're showing here, but I'm not sure if that would meet your complete requirements correctly (and it won't perform all that well).
It would also assume that ToSerialNo is always a string at least as long as FromSerialNo, which may or may not be the case in your data (seems plausible, but I wouldn't want to make that assumption blindly).
Cheers!
- pavandpavan - Friday, June 2, 2017 9:25 AM
Then what are the rules? Should 19 come before 20? Should 190 come before 20? Should 1a come before 10? Please post some sample data and expected results.
John
-
@john-2 Mitchell-245523 - DecoderCode is alpanumeric .
@Jacob Wilkins:-DecoderCode,FromSerialNo and ToSerialNo columns have aplanumeric data. any suggestions regarding solution?Jacob Wilkins - Friday, June 2, 2017 9:34 AMThanks @Jacob Wilkins :- will try that and update.
-
@jacob Wilkins :- Matching Lengths worked and returning accurate results ,But if I try to update full table it is taking forever . I ran the query for 24 hours and still running. My table is big one it has 104825873 rows.
Any Suggestions?? - John Mitchell-245523 - Friday, June 2, 2017 9:37 AM
@john-2 Mitchell :-
90 should come before 100 , 900 should come after 100
A10 should come before 20 ,A100 should come after 20 -
Ok, there are a few points here.
1) The performance problem isn't surprising, since the potential approach I mentioned isn't going to perform well (as I noted originally).
2) The original query we were discussing is a SELECT. It sounds like now you're talking about an UPDATE, so we would need more details to help out (the new query, table definition, index definitions, etc.).
3) The new example comparisons you posted would not be correctly handled by the potential approach I outlined (a string beginning with 'A' will be sorted the same relative to '20' no matter what follows the 'A'). I was only providing a potential approach given the information you had provided, and it seems that information was incomplete in important ways.
It looks like maybe you're wanting to strip out alpha characters, leaving only numeric, and then compare as though they were numeric values, but that's just a guess.
At any rate, we're going to need exact specification of the rules you want implemented (not just a couple examples) and the queries you're trying to optimize to provide much help.
Cheers!
-
Here's a possible workaround. Note that this will prevent the use of index seeks.
CREATE TABLE #Sample(
DecoderCode varchar(20))
INSERT INTO #Sample
VALUES( '4294967296'), ('8589934591'), ('7220817'), ('943529934591'), ( '6294967296');DECLARE @Value1 varchar(20) = '4294967296',
@Value2 varchar(20) = '8589934591';SELECT *
FROM #Sample
WHERE DecoderCode BETWEEN @Value1 AND @Value2;SELECT *
FROM #Sample
WHERE REPLICATE( ' ', 20-LEN(DecoderCode)) + DecoderCode BETWEEN REPLICATE( ' ', 20-LEN(@Value1)) + @Value1 AND REPLICATE( ' ', 20-LEN(@Value2)) + @Value2;UPDATE #Sample
SET DecoderCode = REPLICATE( ' ', 20-LEN(DecoderCode)) + DecoderCode;SELECT *
FROM #Sample
WHERE DecoderCode BETWEEN REPLICATE( ' ', 20-LEN(@Value1)) + @Value1 AND REPLICATE( ' ', 20-LEN(@Value2)) + @Value2;GO
DROP TABLE #Sample; - Eirikur Eiriksson - Friday, June 2, 2017 7:13 AMpavandpavan - Friday, June 2, 2017 7:04 AMI need to get values in between 4294967296 and 8589934591, So in SQL, i am using where column1 between 8589934591 and 8589934591. But in the result values like 7220817 is also returning. It looks like SQL is adding trailing zeros. All columns are varchar datatype and all columns are alplanumeric.
Query&ResultCan you post the actual execution plan and the table DDL please?
😎Any reason for not providing this information?
😎
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply