Solved

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

Posted on 2007-11-15
6
849 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now