Comparision on Char Column With Alphanumeric Values

Hi All
I am using SQL Server 2000.
I have a table which a column ID(Char(13)). The column has values varying
from BBB, AAA, , 432990098C,432990164C, 4329999999999 and so on. When i try
to retrieve records from the
table using a query like
Select * From myTable Where Id >='43299AAAAAAAA' AND Id<='4329999999999'
The query returns 0 records, whereas ideally it should return records with
values like '432999098C','432990164C' and so on.
I have tried all the different collations on the table column but with no
success.
Can some sql guru help me on this.
TIA
Check your expression again - look at this:
IF '43299AAAAAAAA' <= '4329999999999'
PRINT 'True'
ELSE
PRINT 'False'
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:OeV6ZIGwEHA.200@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I am using SQL Server 2000.
> I have a table which a column ID(Char(13)). The column has values varying
> from BBB, AAA, , 432990098C,432990164C, 4329999999999 and so on. When i
try
> to retrieve records from the
> table using a query like
> Select * From myTable Where Id >='43299AAAAAAAA' AND Id<='4329999999999'
> The query returns 0 records, whereas ideally it should return records with
> values like '432999098C','432990164C' and so on.
> I have tried all the different collations on the table column but with no
> success.
> Can some sql guru help me on this.
> TIA
>
|||Thank you for the reponse and you are right the expression would always
evaluate to false and therfore no records are returned.
But the same type of expressions evalutes to true when executed against a
IBM-DB2 database ans returns records as required, Why is that ?
Is there any way where the expression as mentioned by you, would evaluate to
'True' in MS-SQL server and thus return records.
TIA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OxD9rjKwEHA.4004@.tk2msftngp13.phx.gbl...
> Check your expression again - look at this:
> IF '43299AAAAAAAA' <= '4329999999999'
> PRINT 'True'
> ELSE
> PRINT 'False'
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
> news:OeV6ZIGwEHA.200@.TK2MSFTNGP11.phx.gbl...
> try
>
|||I guess there must be some sort order where alfa characters have lower ASCII
number that numbers.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:uqIlscOwEHA.356@.TK2MSFTNGP10.phx.gbl...
> Thank you for the reponse and you are right the expression would always
> evaluate to false and therfore no records are returned.
> But the same type of expressions evalutes to true when executed against a
> IBM-DB2 database ans returns records as required, Why is that ?
> Is there any way where the expression as mentioned by you, would evaluate
to[vbcol=seagreen]
> 'True' in MS-SQL server and thus return records.
> TIA
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
> message news:OxD9rjKwEHA.4004@.tk2msftngp13.phx.gbl...
varying[vbcol=seagreen]
Id<='4329999999999'[vbcol=seagreen]
no
>
|||IF '43299AAAAAAAA' <= '4329999999999' collate SQL_EBCDIC273_CP1_CS_AS
PRINT 'True'
ELSE
PRINT 'False'
I have no idea what the EBCDIC collations do in detail, but they at
least answer this question to the poster's satisfaction.
SK
Dejan Sarka wrote:

>I guess there must be some sort order where alfa characters have lower ASCII
>number that numbers.
>
>
|||THANKS A LOT !!!!!!!!!
SALUTE THE GURU!
"Steve Kass" <skass@.drew.edu> wrote in message
news:4187B2F1.8030001@.drew.edu...[vbcol=seagreen]
> IF '43299AAAAAAAA' <= '4329999999999' collate SQL_EBCDIC273_CP1_CS_AS
> PRINT 'True'
> ELSE
> PRINT 'False'
> I have no idea what the EBCDIC collations do in detail, but they at least
> answer this question to the poster's satisfaction.
> SK
> Dejan Sarka wrote: