Roger Maybe if you use LIKE, you can avoid the whole problem. Eg. select u.property_no, u.road_name, u.postal_code from usr_prop_info u, road_info r where u.postal_code like r.postal_code + '%' and r.district_no = '#form.hdb_estate#' You might need to change the + and/or the % to something else depending on what DBMS you're using. Nick -----Original Message----- From: Roger Lim [mailto:[EMAIL PROTECTED]] Sent: Monday, October 30, 2000 4:03 AM To: CF-Talk Subject: SUBSTR : SQL queries Hi, I encountered problem in executing the below SQL statement : select property_no, road_name, postal_code from usr_prop_info where substr(postal_code,1,length(a.postal_code)) in (select a.postal_code from road_info a where district_no = '#form.hdb_estate#') Apparently, my ROAD_INFO.POSTAL_CODE is either in 2-digit or 3-digit number but my USR_PROP_INFO.POSTAL_CODE is in 6-digit number. I'm trying to do a substr() but its length is determined by the length of the postal_code in ROAD_INFO. How do I able to achieve that ? Thanks. Roger ********************************************************************** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ********************************************************************** ------------------------------------------------------------------------------------------------ Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
