asked on

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

SOLUTION

radja7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jinesh Kamdar

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

David VanZandt

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

sgs1970

ASKER

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.