problems with < or > string values

I have a query that is trying to find all of the names that fall between a low and high value. I'm using CHR(10) for the low value and this seems to work, but using a '{' for the high value does not. I chose '{' because it is one higher then 'z' in the ASCII charts. So the query looks something like this.

SELECT * FROM People where LastName > @.LastNameLow and LastName < @.LastNameHigh

The parameter values are built in .Net code and passed to the stored proceedure. @.LastNameLow is a string of CHR(10) values whose length is equal to the column length. @.LastNameHigh is a string of '{' characters whose length is equal to the column length.

When I run this query, I get no rows returned. However if I change the '{' character to 'z' character, it does return rows. However I don't want to use 'z' because the query is <, not <=. And before you ask, no I cant change it. I've tried '|', '}' and '~', all of which are valid ASCII values above 'z' and valid printable characters, but none of them seem to work in this case.

Any help would be appreciated.

Bill

you want a default LOWEST and HIGHEST value when user does not specify any value is it ?

why not pass in NULL value to @.LastNameLow or @.LastName High if user does not specify any Low or High value

SELECT *
FROM People
WHERE LastName >= coalesce(@.LastNameLow , LastName)
AND LastName <= coalesce(@.LastNameHigh, LastName)|||

A variation on K H Tan's solution (passing in Null when there is no upper or lower limit) is:

SELECT *
FROM People
WHERE ((LastName > @.LastNameLow) OR (@.LastNameLow IS NULL)) AND
((LastName < @.LastNameHigh) OR (@.LastNameHigh IS NULL))

Or if you have to take a string value instead then assuming you use say '@.#@.' as the no limit token then you can use the query:

SELECT *
FROM People
WHERE ((LastName > @.LastNameLow) OR (@.LastNameLow = '@.#@.')) AND
((LastName < @.LastNameHigh) OR (@.LastNameHigh = '@.#@.'))

You can use any token that would not be provided as a legitimate range value. I hope this helps.

|||

Not exactly. This kind of query is essentially being used as a LIKE statement. For instance if the user wants to search last names and enters 'SM' for the value, the parameters would code up to be 'SM'+chr(10)+chr(10)...+chr(10) for @.LastNAme Low and 'SM{{{{...{{{' for @.LastNameHigh. the intent is to return all last names that begin with 'SM'. Like I've said though, I don't really have control over the query and can't change it to a LIKE, so I'm trying to find values that will work. If @.LastNAmeHigh is 'SM{{{{...{{{' we get 0 rows returned, but if @.LastNameHigh is 'SMzzzzzz...zzzz' it does get some rows.

I know this must have something to do with the coallation and sort order set on the sql server. As near as I can tell we are using the default coallation which is Latin 1252 with sort order 52. The LAtin 1252 code page seems to match a traditional ASCII chart, but I have no idea what sort order 52 means or how to tell specifically how it is sorting.

Bill