Board index » delphi » Local SQL problem

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.)

 

Re:Local SQL problem


On 21 Jan 2000 18:04:11 GMT, "N.Andrews" <n...@DELETEMEdatacable.co.uk>
wrote:

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

Re: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

Other Threads