Hi Mark
Your name's familiar - have we met?
It's maybe as simple as this:
Forename is CHAR(20) and the data is left-justified. LOCATE() will therefore
look for 'Andy' followed by 16 spaces and never find it in NAMESTRING. You
need to select locate(TRIM(forename),namestring) to get a match.
Best Regards
Alex Levy
Sustainable Software Ltd.
-----Original Message-----
From: Gillis Mark
[mailto:db2udbdba-ezmlmshield-x59340849.[Email address protected]
Sent: 19 May 2008 12:55
To: LazyDBA Discussion
Subject: LOCATE function
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/
**********************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page