Re: need sql statement for complex address search
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Mon, 30 Jul 2007 13:14:09 -0700
sean nakasone wrote:
we have a database that stores addresses. the address number is stored in a field called MY_NUMBER. Now if the address has two numbers i.e. 12-345, then the 12 will be in a field called MY_PREFIX. I need to come up with 1 SQL statement (and it can't be PL/SQL) that will check if there's a hypen and if so, search in both the MY_PREFIX and MY_NUMBER fields. If there is no hyphen, then it should only search in the MY_NUMBER field. The reason it can't be a PL/SQL statement is because we are using an application that interfaces with oracle and that application will only accept one SQL statement and will not support PL/SQL.
Searches for what?
In what version of Oracle?
Is your intention a query such as this?
create table test (
aid NUMBER(3),
my_prefix VARCHAR2(20),
my_number VARCHAR2(20));
INSERT INTO test VALUES (1, '12','345');
INSERT INTO test VALUES (2, '12',NULL);
INSERT INTO test VALUES (3, NULL,'345');
SELECT aid
FROM test
WHERE (my_prefix || '-' || my_number) = '12-345'
UNION ALL
SELECT aid
FROM test
WHERE my_number = '12-345';
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.
- Follow-Ups:
- Re: need sql statement for complex address search
- From: sean nakasone
- Re: need sql statement for complex address search
- References:
- need sql statement for complex address search
- From: sean nakasone
- need sql statement for complex address search
- Prev by Date: Re: need sql statement for complex address search
- Next by Date: Re: need sql statement for complex address search
- Previous by thread: Re: need sql statement for complex address search
- Next by thread: Re: need sql statement for complex address search
- Index(es):
Relevant Pages
|
