Home » RDBMS Server » Server Administration » Using INSTR to find any letter or any non number
Using INSTR to find any letter or any non number [message #371422] Tue, 17 October 2000 10:43 Go to next message
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 Go to previous messageGo to next message
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.
Re: Using INSTR to find any letter or any non number [message #371439 is a reply to message #371426] Thu, 19 October 2000 07:34 Go to previous message
Neville
Messages: 1
Registered: October 2000
Junior Member
Or use asc(substr('string',val)) function
There are lots of them
You could also use
decode(asc('string'),val2breturned,default)) aslo
Neville
Previous Topic: Array Processing
Next Topic: Conversion into Oracle
Goto Forum:
  


Current Time: Sun Dec 22 15:08:56 CST 2024