Solved

NEED A SYNTAX FOR A SQL ON ALPHA FIELD

Posted on 2007-12-02
5
194 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Updating a table from a temp table 4 36
Oracle SQL - Query help 7 54
How do I refer to a session variable in a query? 4 31
sql server insert 12 30
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

785 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