CODEWITHSUNDEEP
sqloracle-databasearguments
jma
jma

Reputation: 497

Argument '0' is out of range error

I have a query (sql) to pull out a street name from a string. It's looking for the last occurrence of a digit, and then pulling the proceeding text as the street name. I keep getting the oracle

"argument '0' is out of range"

error but I'm struggling to figure out how to fix it.

the part of the query in question is

substr(address,regexp_instr(address,'[[:digit:]]',1,regexp_count(address,'[[:digit:]]'))+2)

any help would be amazing. (using sql developer)

Upvotes: 1

Views: 6441

Answers (3)

MT0
MT0

Reputation: 168232

looking for the last occurrence of a digit, and then pulling the proceeding text as the street name

You could simply do:

SELECT REGEXP_REPLACE( address, '^(.*)\d+\D*$', '\1' )
         AS street_name
FROM   address_table;

Upvotes: 0

user5683823
user5683823

Reputation:

A simpler solution, which does not require separate treatment for addresses without a house number, is this:

with t (address) as (
       select '422 Hickory Str.' from dual union all 
       select 'One US Bank Plaza' from dual
     )
select regexp_substr(address, '\s*([^0-9]*)$', 1, 1, null, 1) as street from t;

The output looks like this:

STREET
-------------------------
Hickory Str.
One US Bank Plaza

The third argument to regexp_substr is the first of the three 1's. It means start the search at the first character of address. The second 1 means find the first occurrence of the search pattern. The null means no special match modifiers (such as case insensitive - nothing like that needed here). The last 1 means "return the first SUBEXPRESSION from the match pattern". Subexpressions are parts of the match expression enclosed in parentheses.

The match pattern has a $ at the end - meaning "anchor at the end of the input string" ($ means the end of the string). Then [...] means match any of the characters in square brackets, but the ^ in [^...] changes it to match any character OTHER THAN what is in the square brackets. 0-9 means all characters between 0 and 9; so [^0-9] means match any character(s) OTHER THAN digits, and the * after that means "any number of such characters" (between 0 and everything in the input string). \s is "blank space" - if there are any blank spaces following a possible number in the address, you don't want them included right at the beginning of the street name. The subexpression is just [^0-9]* meaning the non-digits, not including any spaces before them (because the \s* is outside the left parenthesis).

My example illustrates a potential problem though - sometimes an address does, in fact, have a "number" in it, but spelled out as a word instead of using digits. What I show is in fact a real-life address in my town.

Good luck!

Upvotes: 0

Aleksej
Aleksej

Reputation: 22959

The fourth parameter of regexp_instr is the occurrence:

occurrence is a positive integer indicating which occurrence of pattern in source_string Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern.

In this case, if an address has no digits within, the regexp_count will return 0, that's not a valid occurrence.

Upvotes: 1

Related Questions