Separate house number and addition in oracle SQL

Question

I have a problem with my Oracle SQL string and don't get the correct result. I have a table with Housenumber and addition in one field, i.e. 16f

As result I want it in 2 Fields:

Housenumber  Addition
16           f

Housenumber is a Number (1 or more digits)
Addition is a Letter

I have the same Problem with the Fields Ortsname and Ortszusatz there it works. But I can't get it with the Housenumber. the result is a duplication of my entries.

WITH TEST_DATA AS
   (SELECT distinct '*' Ort, Nummer FROM adresses)

SELECT 
        Houseid,
        Streetid,
        Gemeindeschl,
        Gemeinde,
        Bundesland,
        Landkreis,        
        REGEXP_SUBSTR(t.Ort, '[^,]+', 1, 1) Ort,
        REGEXP_SUBSTR(t.Ort, '[^,]+', 1, 2) Ortszusatz,
        Strasse,
        regexp_substr(t.Nummer, '[^0-9,]',1, 1) Housenumber,
        regexp_substr(t.Nummer, '[^A-Z,]',1, 2) Addition,
        Objektkl,
        Lng,
        Lat,
        Plz
FROM adresses T

Why do you use exclusion to extract substring of numbers? This `[^0-9,]` will select everything except numbers or comma. Should be `[0-9]+` or simply `\d+`. And you do not need to specify 1 as a astarting position and 1 as the occurrence number because this is a default (it is a universal assumption to extract the first match by default) — astentx, Aug 10 '22 at 10:22
@astentx it works so far, but for example with the entries of Housenumber 10, 10b, 10c and 10d I get the result: 10 | (null) , 10 | (null), 10 | b, 10 | c, 10 | d I get 2 times **10 | (null)** — embi, Aug 10 '22 at 10:32
You didn't provide any sample data, but input of 4 rows cannot produce output of 5 rows. See https://dbfiddle.uk/?rdbms=oracle_21&fiddle=cd22165e6b3d26ede2becb3cee107299 — astentx, Aug 10 '22 at 10:43

score 0 · Answer 1 · answered Aug 10 '22 at 10:21

As you said - fetch "digits" and "letters" separately:

SQL> with addresses (houseid, housenumber) as
  2    (select 1, '16f' from dual union all
  3     select 2, '20'  from dual
  4    )
  5  select houseid,
  6    regexp_substr(housenumber, '[[:digit:]]+') housenumber,
  7    regexp_substr(housenumber, '[[:alpha:]]+') addition
  8  from addresses;

   HOUSEID HOUSENUMBER     ADDITION
---------- --------------- ---------------
         1 16              f
         2 20

SQL>

Separate house number and addition in oracle SQL

1 Answers1