Sql Query for generic search - Look for search string followed by atleast one alphanumeric character

 Hi,
  I am developing a vb.net GIS application with oracle backend . I need to make  a generic query for the house numbers.    (House_No is a column of string type)
   
   The query is to return any house_no starting with search string followed by atleast one alphanumeric character

   For example a query for house_no like  21   should return 21,21-1,21/1  
    but should not return 211 or 211-1 etc  
    That is, return any house_no starting with 21 followed by atleast one alphanumeric character

    Thanking you,
           Sam  
 
   
sgs1970Asked:
Who is Participating?
 
Jinesh KamdarConnect With a Mentor Commented:
radja's query with a slight modification should work just fine. Substitute ur variable wherever i have put in the actual search values.
SQL> SELECT * FROM jaktest ORDER BY house_no;
 
HOUSE
-----
121
121-1
1211
2
2-1
2/1
2/2
21
21-1
21/1
211
211-1
 
12 rows selected.
 
SQL> SELECT * FROM jaktest
  2  WHERE (house_no = '2') 
  3  OR (house_no LIKE '2' || '%' AND TRANSLATE(SUBSTR(house_no,LENGTH('2')+1,1),'0123456789','0000000000')!='0');
 
HOUSE
-----
2
2/1
2/2
2-1
 
SQL> SELECT * FROM jaktest
  2  WHERE (house_no = '21') 
  3  OR (house_no LIKE '21' || '%' AND TRANSLATE(SUBSTR(house_no,LENGTH('21')+1,1),'0123456789','0000000000')!='0');
 
HOUSE
-----
21
21-1
21/1
 
SQL> SELECT * FROM jaktest
  2  WHERE (house_no = '121') 
  3  OR (house_no LIKE '121' || '%' AND TRANSLATE(SUBSTR(house_no,LENGTH('121')+1,1),'0123456789','0000000000')!='0');
 
HOUSE
-----
121
121-1
 
SQL> SELECT * FROM jaktest
  2  WHERE (house_no = '211') 
  3  OR (house_no LIKE '211' || '%' AND TRANSLATE(SUBSTR(house_no,LENGTH('211')+1,1),'0123456789','0000000000')!='0');
 
HOUSE
-----
211-1
211
 
SQL> 

Open in new window

0
 
radja7Connect With a Mentor Commented:
Something like this:
WHERE house_no LIKE &search_str || '%'
AND translate(substr(house_no,length(&search_str)+1,1),'0123456789','0000000000')!='0'

Good luck!
0
 
Jinesh KamdarCommented:
>> That is, return any house_no starting with 21 followed by atleast one alphanumeric character
Acc. to the above stmt., your required result could also contain 211, 211-1, etc. since the 1 followed by the 21 IS an alpha-NUMERIC char. Pls be more specific in ur exact requirements.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
DavidSenior Oracle Database AdministratorCommented:
WHERE REGEXP_INSTR (house_no, '[[^[:digit:]]]{3}$') will return values where the third digit is not numeric, based upon your example.  

See: http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
0
 
sgs1970Author Commented:

 Hi Guys,
   I apologize on the  misleading   "alphanumeric "   part of the question . Infact, what I  intented was non-numeric .  Thanks to jinesh_kamdar for pointing out this.
0
 
sgs1970Author Commented:
Hi,
  Regarding the answer, unfortunately I couldn't figure out one yet among the given.
   
    Answet given by dvz doesn't help me because the search could be for house no for 2 and it should return 2,2/1,2/2,2-1 etc... . Also search for 121 should return 121,121-1 etc...

   
            Thanking you,
            Sam
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.