NEED A SYNTAX FOR A SQL ON ALPHA FIELD

I have table  wich contains a key wich is alpha/nummeric, table will have value from  "        1600"  untill "      1900"
field is 12 digits and right aligned with leading spaces
when i call:
SELECT * FROM CREDOPEN where key3  like '%1673%'  I get a perfect list from all record wich has  "      1673"
but I can't figure out how to get all   from  1673 untill e.g.   1840
if I call

SELECT * FROM CREDOPEN where key3  >='1673' and   key3 =< '1840'  I get nothing
what do I wrong in syntax for this kind off search
(p.s.  I have pervasive on windows-xp as record manager /odbc)
 
BIAPROAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
imitchieConnect With a Mentor Commented:
SELECT * FROM CREDOPEN where ltrim(key3)  >='1673' and   ltrim(key3) <= '1840'
0
 
imitchieCommented:
SELECT * FROM CREDOPEN where ltrim(key3)  >='1673' and   ltrim(key3) =< '1840'
0
 
BIAPROAuthor Commented:
true the one with =<   gives error, the other one perfect
Thanks a lot
Regards Jack
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: in case you have values that are not 4 digits, only using ltrim() will give "bad" results...
you should consider casting the string value to a numerical value... not sure what functions pervasive offers you for that, but I guess CAST() should work:


SELECT * FROM CREDOPEN where cast(ltrim(key3) as INT)  >= 1673 and   cast(ltrim(key3) as int) <= 1840

Open in new window

0
 
BIAPROAuthor Commented:
Ok thanks for tip, will try out
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.