[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

NEED A SYNTAX FOR A SQL ON ALPHA FIELD

Posted on 2007-12-02
5
Medium Priority
?
222 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 2000 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 143

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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

590 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