sql - 多次拆分并分配 order_id

Question

我有一些地址字符串需要针对每个组件进行分析。

SQL Fiddle Demo

CREATE TABLE Table1 ("ID" int, "address" varchar(41));

INSERT INTO Table1 ("ID", "address")
VALUES
    (1, 'calle 15, lomas del avila'),
    (2, 'calle av. solano edif:Apalache apt-15');

所以我需要按不同的字符拆分，例如(.、,、:、;、<space>、-)

对于一个角色，我知道该怎么做。

SELECT ID, s.token
FROM   Table1 t, unnest(string_to_array(t.address, ' ')) s(token);

如何链接多个 unnest 以及如何分配 OrderID？

| ID |         token | orderID
|----|---------------|--------
|  1 |         calle |    1
|  1 |           15, |    2
|  1 |         lomas |    3
|  1 |           del |    4
|  1 |         avila |    5
|  2 |         calle |    1
|  2 |           av. |    2
|  2 |        solano |    3
|  2 | edif:Apalache |    4
|  2 |        apt-15 |    5

对于此示例，第二行 15,将拆分为 15和 null所以第二个结果可以被丢弃并且顺序不会改变。

但在最后两行edif:Apalache和 apt-15第二次拆分将产生 edif , Apalache , apt和 15所以 orderID 将从 4 变为 7:

|  2 | edif     |    4
|  2 | Apalache |    5
|  2 | apt      |    6
|  2 | 15       |    7

Answer 1

使用translate() :

SELECT "ID", token, row_number() over (partition by "ID")
FROM (
    SELECT "ID", s.token
    FROM   
        Table1 t, 
        unnest(string_to_array(translate(t.address, '.,:;-', '     '), ' ')) 
            s(token)
    WHERE token <> ''
    ) sub

SqlFiddle .