Solved

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

Posted on 2007-11-15
6
857 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 100 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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 150 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 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