October 6, 2017 at 1:40 pm
I have a table with serial # (which is a varchar column). If i have to find a range from this table can i use between operator?
Table will have the below structure:
1. Part # Varchar(40)
2. Serial # Varchar(40)
3. Quantity int
...
how to find the range of serials between "004-ABC" and "78B-343" ?
October 6, 2017 at 1:53 pm
sathish802004 - Friday, October 6, 2017 1:40 PMI have a table with serial # (which is a varchar column). If i have to find a range from this table can i use between operator?Table will have the below structure:
1. Part # Varchar(40)
2. Serial # Varchar(40)
3. Quantity int
...how to find the range of serials between "004-ABC" and "78B-343" ?
Sure you can, though whether it returns the results you are hoping for is another matter.DECLARE @x TABLE (SomeText VARCHAR(50));
INSERT @x
(
SomeText
)
VALUES
('004-ABC')
,('78B-343')
,('1')
,('Z')
,('3')
,('9');
SELECT *
FROM @x x
WHERE
x.SomeText BETWEEN '004-ABC' AND '78B-343';
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2017 at 8:38 am
BETWEEN on a character column works different than it would with numeric or date/time columns
'0'
'1'
'111111111'
'2'
'2222222'
WHERE col BETWEEN '1' AND '2'
would return:
'1'
'111111111'
'2'
Character sorting works on individual characters from left to right (depending on language settings), just like a dictionary sorts words. The word length doesn't matter in the sort, only the character in the column being sorted.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply