Solved

NEED A SYNTAX FOR A SQL ON ALPHA FIELD

Posted on 2007-12-02
5
187 Views
Last Modified: 2010-04-21
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
Comment
Question by:BIAPRO
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20391863
SELECT * FROM CREDOPEN where ltrim(key3)  >='1673' and   ltrim(key3) =< '1840'
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20391865
SELECT * FROM CREDOPEN where ltrim(key3)  >='1673' and   ltrim(key3) <= '1840'
0
 

Author Closing Comment

by:BIAPRO
ID: 31412208
true the one with =<   gives error, the other one perfect
Thanks a lot
Regards Jack
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20391949
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
 

Author Comment

by:BIAPRO
ID: 20392250
Ok thanks for tip, will try out
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now