January 7, 2004 at 10:12 am
create table MyTestTable (MyLastNameField varchar(60))
insert into MyTestTable SELECT 'ZEA'
select * from MyTestTable
select * from MyTestTable
where MyLastNameField between char(0) and replicate(char(255),60)
I'm not understanding why this statement will not return the row. If you change the replicate function to char(90) equivalent to the 'Z' character, the row will be returned. Why doesn't the char(255) work?
Basically what I'm trying to accomplish is to select rows where the LastNameField is between (the smallest possible value that a varchar(60) fields can hold) and (the largest possible value that a varchar(60) field can hold). These values are actually going to be parameters passed into a stored procedure and if they are passed as null, then I want to set them to the minimum and maximum values.
Any ideas?
January 7, 2004 at 10:32 am
Your default collation is not for binary sort order. Try:
WHERE MyLastNameField BETWEEN CAST(CHAR(0) AS char(1)) COLLATE Latin1_General_BIN
AND CAST(REPLICATE(CHAR(255),60) AS varchar(60)) COLLATE Latin1_General_BIN
or:
CREATE TABLE MyTestTable(MyLastNameField varchar(60)) COLLATE Latin1_General_BIN)
--Jonathan
January 7, 2004 at 10:52 am
Thank you very much for the fast reply! This would have taken me a real long time to figure out.
January 7, 2004 at 3:29 pm
Just as an FYI converting the varchar fields to varbinary also works.
where convert(varbinary(60),MyLastNameField) between convert(varbinary(60),char(0)) and convert(varbinary(60),replicate(char(255),60))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply