Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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)
 
0
BIAPRO
Asked:
BIAPRO
  • 2
  • 2
1 Solution
 
imitchieCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now