Local SQL problem 
Author Message
 Local SQL problem

I have a couple problems with Borland local SQL:

Firstly: UK postcodes usually start with two letters indicating the area,
so for example if I'm looking for Leeds addresses I can filter these with a
query like:

SELECT * FROM ADDLIST.DBF  WHERE ADDLIST.DBF."POSTCODE"  LIKE "LS%" ;

However, some cities (e.g. Sheffield) have postcodes that start with a
single letter before digits. I would like to use a query like

SELECT * FROM ADDLIST.DBF WHERE ADDLIST.DBF."POSTCODE" LIKE "S[1-9]%";

to pick these out, but it doesn't work because "S[1-9]%" gets treated as a
literal, and the query doesn't find any postcodes. Anyone know a way round
this?

Secondly: can any helpful soul tell me how to extend this query into MEMO
fields?

Thanks in advance, Nick Andrews. (Please don't email me because it isn't
working.)



Wed, 18 Jun 1902 08:00:00 GMT
 Local SQL problem



Quote:
>I have a couple problems with Borland local SQL:

>Firstly: UK postcodes usually start with two letters indicating the area,
>so for example if I'm looking for Leeds addresses I can filter these with a
>query like:

>SELECT * FROM ADDLIST.DBF  WHERE ADDLIST.DBF."POSTCODE"  LIKE "LS%" ;

>However, some cities (e.g. Sheffield) have postcodes that start with a
>single letter before digits. I would like to use a query like

>SELECT * FROM ADDLIST.DBF WHERE ADDLIST.DBF."POSTCODE" LIKE "S[1-9]%";

>to pick these out, but it doesn't work because "S[1-9]%" gets treated as a
>literal, and the query doesn't find any postcodes. Anyone know a way round
>this?

I know of no way to express that directly in standard SQL or local SQL. You
might be able to reword the query to something like that below.

  SELECT *
  FROM "ADDLIST.DBF"
  WHERE (SUBSTRING(POSTCODE FROM 1 FOR 1) = "S") AND
    (SUBSTRING(POSTCODE FROM 2 FOR 1) IN ("1", "2", "3", "4", "5", "6",
    "7", "8", "9", "0"))

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx



Wed, 18 Jun 1902 08:00:00 GMT
 Local SQL problem

It worked! although not quite as concise as it would be
in an ideal world..
Thanks, Steve. And greetings from halfway between Leeds
and Sheffield , U.K.

Quote:

> I know of no way to express that directly in standard SQL or local SQL.
You
> might be able to reword the query to something like that below.

>   SELECT *
>   FROM "ADDLIST.DBF"
>   WHERE (SUBSTRING(POSTCODE FROM 1 FOR 1) = "S") AND
>     (SUBSTRING(POSTCODE FROM 2 FOR 1) IN ("1", "2", "3", "4", "5", "6",
>     "7", "8", "9", "0"))

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Quote:
> Steve Koterski              "Health nuts are going to feel stupid
someday,
> Felton, CA                  lying in hospitals dying of nothing."
>                                                               -- Redd
Foxx



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Local SQL problem.

2. Local SQL problem with outer join

3. Tricky Local SQL Problem ?????

4. Local SQL problem

5. Local SQL, problem with OR operator in WHERE clause

6. Paradox - local SQL problem with insert

7. HELP!!:TQuery/Local SQL Problem

8. 2 Local SQL Problems

9. Local SQL problems with BDE and TQuery

10. Delphi Local SQL Problem !! Please Help Me

11. Local SQL - problem with query using a DATE

12. Query problem with local SQL


 
Powered by phpBB® Forum Software