[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

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  
 
   
0
sgs1970
Asked:
sgs1970
2 Solutions
 
radja7Commented:
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
Jinesh KamdarCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now