?
Solved

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

Posted on 2007-11-15
6
Medium Priority
?
861 Views
Last Modified: 2008-02-01
 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  
 
   
0
Comment
Question by:sgs1970
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Assisted Solution

by:radja7
radja7 earned 400 total points
ID: 20287944
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20288046
>> 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
 
LVL 23

Expert Comment

by:David
ID: 20293166
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:sgs1970
ID: 20303586

 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
 

Author Comment

by:sgs1970
ID: 20303833
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
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 600 total points
ID: 20311264
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question