Hi Experts
I'm trying to write some SQL to return rows where one value exists
within another. LOCATE seemed like the way to do this but it doesn't
(always) seem to handle a search for a value stored in a column in one
table, within a column in another table.
Example
Table 1 is PERSON and has 2 CHAR(20) columns: FORENAME and SURNAME
Table 2 is POB_DETAILS and has 1 CHAR(20) column: NAMESTRING
There are rows in PERSON containing the value 'Andy':
FORENAME SURNAME
-------------------- --------------------
Andy Smith
Andy York,
and one in POB_DETAILS
NAMESTRING
--------------------
Andy York, via Worc
Andy Smith +
I thought that LOCATE(ForeName,Namestring) would return a value of 1 for
both of these rows, but it doesn't. However, if I hard-code the search
string, it works:
select namestring, locate('Andy',namestring) from pob_details
Andy Smith + 1
Andy York, via Worc 1
What further complicates the matter is that the LOCATE using the 2
columns works some of the time; it appears to LOCATE successfully when
expression2 is a string containing only 1 value.
Has anyone else had this sort of problem and found a way round it? Is
there a limitation on LOCATE that means it can only search a single
contiguous string?
Any help appreciated.
Mark Gillis
**********************************************************************
This email (and any attachments) may contain privileged and/or confidential information. If you are not the intended recipient please do not disclose, copy, distribute, disseminate or take any action in reliance on it. If you have received this message in error please reply and tell us and then delete it. Should you wish to communicate with us by email we cannot guarantee the security of any data outside our own computer systems. For the protection of Legal & General's systems and staff, incoming emails will be automatically scanned. Any information contained in this message may be subject to applicable terms and conditions and must not be construed as giving investment advice within or outside the United Kingdom.
Legal & General Group plc is registered in England under company number 1417162 and is a holding company.
The registered office for all companies in the Legal & General group is One Coleman Street London EC2R 5AA.
The following subsidiary companies of Legal & General Group Plc are authorised and regulated by the Financial Services Authority: Legal & General Partnership Services Limited, Legal & General Insurance Limited, Legal & General Assurance Society Limited, Legal & General (Unit Trust Managers) Limited and Legal & General (Portfolio Management Services) Limited.
Legal & General International (Ireland) is incorporated in Ireland under company number 440141 with its registered office at Alexandra House, The Sweepstakes, Ballsbridge, Dublin 4 and is authorised by the Financial Regulator in Ireland and by the Financial Services Authority for the conduct of insurance business in the UK.
Full details can be found at http://www.legalandgeneralgroup.com/
**********************************************************************
DB2 & UDB email list listserv db2-l LazyDBA home page