Ranganath, You could use either
SELECT * FROM TAB1 WHERE INSTR(SHIP_NAME, SHIP_ID) > 0 or SELECT * FROM TAB1 O FROM WHERE SHIP_NAME LIKE '%'||(SELECT SHIP_ID FROM TAB1 WHERE ROWID = O.ROWID)||'%' I did a test in my system; the INSTR approach was far better than the LIKE one. n the latter the response time was twice as much and consistent reads were 100 times as much. HTH. Arup ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 17, 2002 5:38 AM > Hi all, > > I have a table by name shipment with two columns say ship_id and > ship_name and data as follows: > > SHIP_ID SHIP_NAME > ------------- ------------------- > ABC 1ABCLMN > XYZ 23XYZ1112 > ABB 123LM > > I want to select all the columns in shipment table which has ship_id > as string in the ship_name column. For example, ship_id 'ABC' is present in > '1ABCLMN'. So that record should be selected. The ship_id string may start > from any position and end at any position in ship_name column. So I cannot > use the substr function in this case. How do I write the select clause for > this requirement? > > Any help in this regard is very much appreciated. > > Thanks and Regards, > > Ranganath > WARNING: The information in this message is confidential and may be legally > privileged. It is intended solely for the addressee. Access to this message > by anyone else is unauthorised. If you are not the intended recipient, any > disclosure, copying, or distribution of the message, or any action or > omission taken by you in reliance on it, is prohibited and may be unlawful. > Please immediately contact the sender if you have received this message in > error. Thank you. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Krishnaswamy, Ranganath > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
