CODEWITHSUNDEEP
sqloracle-database
Rocky
Rocky

Reputation: 1

Extracting numbers from a string without the following characters in SQL

So I have street addresses like the following:

123 Street Ave
1234 Road St Apt B
12345 Passage Way

Now, I'm having a hard time extracting just the street numbers without any of the street names.

I just want:

123
1234
12345

Upvotes: 0

Views: 49

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

The way you put it, two simple options return the desired result. One uses regular expressions (selects the first number in a string), while another one returns the first substring (which is delimited by a space).

SQL> with test (address) as
  2    (select '123 Street Ave' from dual union all
  3     select '1234 Road St Apt B' from dual union all
  4     select '12345 Passage Way' from dual
  5   )
  6  select
  7    address,
  8    regexp_substr(address, '^\d+') result_1,
  9    substr(address, 1, instr(address, ' ') - 1)  result_2
 10  from test;

ADDRESS            RESULT_1           RESULT_2
------------------ ------------------ ------------------
123 Street Ave     123                123
1234 Road St Apt B 1234               1234
12345 Passage Way  12345              12345

SQL>

Upvotes: 1

Related Questions