Converting string with DIN 1421 pattern to human-readable

Question

In the Oracle DB there is a table that stores "DIN"s attribute (VARCHAR2(1200 BYTE)), see a table/image below.
Each string follows the DIN 1421 pattern.

+------------------+
|       DIN        |
+------------------+
| 0004.01.03.00.00 |
| 0006.06.00.00.00 |
| 0002.02.01.00.00 |
| 0002.02.02.00.00 |
| 0349.00.00.00.00 |
| 0004.00.01.00.00 |
| 0001.05.20.10.30 |
| ...              |
+------------------+

Now, I am trying to convert this string to more human-readable format, i.e.

0004.01.03.00.00 → 4.1.3
0006.06.00.00.00 → 6.6
0002.02.01.00.00 → 2.2.1
0002.02.02.00.00 → 2.2.2
0349.00.00.00.00 → 349
0004.00.01.00.00 → 4.0.1
0001.05.20.10.30 → 1.5.20.10.30

How can I achieve this?

I can imagine myself working with the combination of to_number() and substr() functions and applying each time the CASE expression. But there must be more efficient and meaningful solution, is not it?

Answer 1

Shorter code is not always more efficient and meaningful. This may run slower and may be more difficult to understand. I guess someone can construct a single regular expression for this task which will be even more unpleasant to read.

with d as 
(
select '0004.01.03.00.00' data from dual union all
select '0006.06.00.00.00' data from dual union all
select '0002.02.01.00.00' data from dual union all
select '0002.02.02.00.00' data from dual union all
select '0349.00.00.00.00' data from dual union all
select '0000.05.00.00.00' data from dual union all
select '0000.00.20.10.30' data from dual
)
select 
  (
    select
      listagg(ltrim(regexp_substr(data,'[^.]+', 1, level), 0), '.')
        within group (order by null)
    from
      dual
    connect by
      regexp_substr(data, '[^.]+', 1, level) is not null
  ) as data
from
  d
;

DATA                
--------------------
4.1.3
6.6
2.2.1
2.2.2
349
5
20.10.30

Answer 2

I does not work yet for 0004.00.01.00.00.

The query that I have in mind:

with d as 
(
select '0004.01.03.00.00' data from dual union all
select '0006.06.00.00.00' data from dual union all
select '0002.02.01.00.00' data from dual union all
select '0002.02.02.00.00' data from dual union all
select '0349.00.00.00.00' data from dual union all
select '0004.00.01.00.00' data from dual union all
select '0001.05.20.10.30' data from dual
)


SELECT
    d.data || ' -> ' ||
    TRIM(LEADING '.' 
            FROM to_number(ltrim(regexp_substr(d.data, '[^.]+', 1), 0))||
            NVL2(to_number(ltrim(regexp_substr(d.data, '[^.]+', 6), 0)),'.'||to_number(ltrim(regexp_substr(d.data, '[^.]+', 6), 0)),to_number(ltrim(regexp_substr(d.data, '[^.]+', 6), 0)))||
            NVL2(to_number(ltrim(regexp_substr(d.data, '[^.]+', 9), 0)),'.'||to_number(ltrim(regexp_substr(d.data, '[^.]+', 9), 0)),to_number(ltrim(regexp_substr(d.data, '[^.]+', 9), 0)))||
            NVL2(to_number(ltrim(regexp_substr(d.data, '[^.]+', 12), 0)),'.'||to_number(ltrim(regexp_substr(d.data, '[^.]+', 12), 0)),to_number(ltrim(regexp_substr(d.data, '[^.]+', 12), 0)))||
            NVL2(to_number(ltrim(regexp_substr(d.data, '[^.]+', 15), 0)),'.'|| to_number(ltrim(regexp_substr(d.data, '[^.]+', 15), 0)), to_number(ltrim(regexp_substr(d.data, '[^.]+', 15), 0))))
            AS "Concatenated String"
FROM d
WHERE d.data IS NOT NULL;

result into this:

+----------------------------------+
|        Concatenated String       |
+----------------------------------+
| 0004.01.03.00.00 -> 4.1.3        |
| 0006.06.00.00.00 -> 6.6          |
| 0002.02.01.00.00 -> 2.2.1        |
| 0002.02.02.00.00 -> 2.2.2        |
| 0349.00.00.00.00 -> 349          |
| 0004.00.01.00.00 -> 4.1          |  
| 0001.05.20.10.30 -> 1.5.20.10.30 |
+----------------------------------+

---

References:

• Is there an equivalent to concat_ws in oracle?