Not one function, but a combination of two should serve your purpose...
select A.event_id, to_char(A.event_date,'mm/dd/yyyy hh24:mi:ss'),
A.event_type, A.event,
B.name, B.description
from table_a A, table_b B
where upper(B.name) = substr(upper(a.event), instr(a.event, '.', -1), 2)
A quick explanation,
substr(string, start position, length)
We know the field and the length, so now we need the start position
instr(string, field to find, start position)
Putting a start position of -1 tells oracle to work from the end of the
list.
Hope this works, since I don't have your data to test. If you still have
problems, let me know.
Steve
-------------------------------------
Steven Monaghan
Oracle DBA / Cold Fusion Developer
MSC Industrial Direct Co., Inc.
Melville, NY
[EMAIL PROTECTED]
http://www.mscdirect.com
-------------------------------------
-----Original Message-----
From: Scott Mulholland [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 24, 2001 10:46 AM
To: CF-Talk
Subject: SQL - Oracle Functions
Let me see if I can explain this clearly...I am trying to join 2 tables,
using a field from table A to match just a part of a field in table B
(re-working of my tables and what I store will fix this, but I want to see
if it can be done in my current setup)
select A.event_id, to_char(A.event_date,'mm/dd/yyyy hh24:mi:ss'),
A.event_type, A.event,
B.name, B.description
from table_a A, table_b B
where B.name = "a part of A.event"
A.event is a long string of data, stored from a system log, which I can
parse out the name for display purposes with the following cf code:
#Ucase(ListLast(GetToken(event, 2, chr(10)), '.'))#
select A.event_id, to_char(A.event_date,'mm/dd/yyyy hh24:mi:ss'),
A.event_type, A.event,
B.name, B.description
from table_a A, table_b B
where upper(B.name) = '#Ucase(ListLast(GetToken(A.event, 2, chr(10)),
'.'))#'
The above does not work, since it does not know the value of A.event.
Are there any Oracle functions that can achieve this result?
If not I will just rework the table to parse out the name prior to insertion
and store it in table A to do a straight join (A.name = B.name)
Thanks,
Scott
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
