| Using INSTR to find any letter or any non number [message #371422] |
Tue, 17 October 2000 10:43  |
Mike Browning
Messages: 1
Registered: October 2000
|
Junior Member |
|
|
I have a query I'm trying to convert from SQL Server to Oracle. It does some fancy things in the order by clause to sort building names the way we want it to. The sort looks at the building name before the address part (i.e. 100 Permiter Park gets sorted with the P's instead of by 1). I'm struggling to find a matching function in Oracle for what is working in SQL Server.
In SQL Server:
patindex('%[[^0-9]]%',building_name)
In Oracle:
instr(building_name, ?)
There's more to the search, but this is the core part I'm having trouble with. Basically, I'm trying to find the first occurence in the building_name that is not a number. That allows me to ignore the building's address number. I can't find something equivalent in Oracle that will denote a range of characters.
Any help will be greatly appreciated.
|
|
|
|
| Re: Using INSTR to find any letter or any non number [message #371426 is a reply to message #371422] |
Tue, 17 October 2000 12:36  |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Mike,
Use this instead of just instr
INSTR(TRANSLATE(BUILDING_NAME, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'X')
All this does is to translate every occurance of a A-Z or a-z TO X. If your building name was
100, Links Road
the translate function will convert this into
100, XXXXX XXXX.
The instr function just looks for the first occurance of 'X'. If you are sure the building names always start with caps, you can remove the abcde.... set and also remove 26 'X' from the loooong list of XXXXXX....
hth
Prem :)
p.s. There are totally 52 X in the list.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
