CODEWITHSUNDEEP
sqlpostgresql
Phill Pafford
Phill Pafford

Reputation: 85298

Find Match in Database within the Field

Part two of this question:

NOTE: I'm running Postgres 7.4 and yes we are upgrading

Example Data

    address            |    zip
-----------------------+-------------+
123 main street        |    12345
-----------------------+-------------+
23 where road          |    12345
-----------------------+-------------+
South 23 where lane    |    12345

The query

SELECT address
FROM tbl
WHERE zip = 12345
AND LOWER(substring(address, '^\\w+')) = LOWER('lane')

Also tried

SELECT address
FROM tbl
WHERE zip = 12345
AND LOWER(substring(address, '\\w+')) = LOWER('lane')

SELECT address
FROM tbl
WHERE zip = 12345
AND LOWER(substring(address, '^\\w')) = LOWER('lane')

SELECT address
FROM tbl
WHERE zip = 12345
AND LOWER(substring(address, '\\w')) = LOWER('lane')

I would like to be able to search for any part of the address in the address column. So if I needed all rows that had the word lane, I could pass lane and the zip to return all the rows. This would give me:

    address            |    zip
-----------------------+-------------+
South 23 where lane    |    12345

Or if I needed all the rows with and address of 23, this would give me:

    address            |    zip
-----------------------+-------------+
23 where road          |    12345
-----------------------+-------------+
South 23 where lane    |    12345

Is there something I could change in the example query above to allow this?

Upvotes: 0

Views: 85

Answers (2)

Maarten van Leunen
Maarten van Leunen

Reputation: 452

I suppose you could try something like:

SELECT * 
FROM tbl 
WHERE zip = 12345 
AND (address like '% ' + 'lane' + ' %' -- check in middle 
or address like 'lane' + ' %' -- check at beginning
or address like '% ' + 'lane') -- check at end

Bt I'd advise against it.

If you're this far into troubles, you might have better luck looking at "full text search" capabilities, especially since performance isn't going to be great.

I hear the new Postgres has support for that, though.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95522

It depends on what you mean by "all rows that had the word lane". A single regular expression probably isn't going to work here.

with tbl as (
    select '123 main street' address, '12345' zip
    union all
    select '23 where road', '12345' zip
    union all
    select 'South 23 where lane', '12345' zip
    union all
    select '245 Meadowlane Dr', '12345'
    union all 
    select '764 Pine Lane Ave', '12345'
)
select * from tbl
where zip = '12345' 
  and (
          address ~* '^lane .*' 
       or address ~* '.* lane .*'
       or address ~* '.* lane$'
      );

address               zip
--
South 23 where lane   12345
764 Pine Lane Ave     12345

That kind of regular expression won't return "123 main street" if you use "23" instead of "lane" in the WHERE clause. But it also won't let you query for all the addresses in the 2300 block of Maple St. Apartment numbers and PO Box numbers can also surprise you.

Upvotes: 1

Related Questions