I was given a large list of first and last names and asked to find how many of them are in the database. Because most of names in the database are listed with a middle initial, I want to take the group and make a list using a wildcard between the first and last names. So, I want my query to be something like:
Select * from table
where name like (‘John%Smith’, ‘Mary%Clark’, ‘Ricard%Evans’)
When I run this I receive an error ORA-00907 which states a left parenthesis has been entered without a closing right parenthesis, or extra information was contained in the parentheses. All parentheses must be entered in pairs.
I have both sets of parenthesis so I’m assuming that trying to have multiple names with the use of Like is throwing the query off. Any thoughts on how to get around this?
where name like ('John%Smith', 'Mary%Clark', 'Ricard%Evans')
This one is an easy one. You missed a rule, LIKE can only be used with a single
value.
In short, you can have:
… LIKE ‘JOHN%SMITH’
Or
… IN (‘JOHN SMITH’, ‘MARY CLARK’)
The work around would be multiple OR clauses:
… LIKE ‘JOHN%SMITH’
OR LIKE ‘MARY%CLARK’
However, you mentioned you were given a large list of names, so it might very
well be easier to load those into a dataset and work with that instead. You
could use an actual table, or you could collections, whatever strikes your
fancy.
I was given a large list of first and last names and asked
to find how many of them are in the database. Because most
of names in the database are listed with a middle initial, I
want to take the group and make a list using a wildcard
between the first and last names. So, I want my query to be
something like:
Select * from table
where name like ('John%Smith', 'Mary%Clark', 'Ricard%Evans')
What I would do in this situation is:
Load the list of names into a work table in the database.
Replace all single/multiple spaces in the names by a '%'.
for each row found in the work table, find a list of matching names in
the main table.
Note the name looked for and the list found, or note that nothing was
found.
Repeat.
Something like the following - although you could write the names
found/not found to another working table if you don't have unlimited
DBMS_OUTPUT (ie, if you are on 9i and less).
DECLARE
TYPE tNames IS TABLE OF pswd_server.svr_servername%TYPE;
vNames tNames;
BEGIN
-- a 'pretend' table of server names with wild card characters
present.
FOR x IN (SELECT 'ealdcl0%' AS NAME FROM dual UNION ALL
SELECT 'ldprwl0%' FROM dual UNION ALL
SELECT 'not%found' FROM dual) LOOP
SELECT svr_servername
BULK COLLECT
INTO vNames
FROM pswd_server
WHERE svr_servername LIKE x.NAME;
IF (vNames.count <> 0) THEN
FOR y IN vNames.first .. vNames.last LOOP
dbms_output.put_line(x.NAME || ' = ' || vNames(y));
END LOOP;
ELSE
dbms_output.put_line(x.NAME || ' = no records found' );
END IF;
END LOOP;
END;
Sorry in advace for Outlook's ability to remove indentation!
Cheers,
Norm [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk