I parse string in Oracle but names of providers have a special characters like '&' and '-'. And i can't handle with it. Could anyone help me ?
SELECT arraylist.* , inserted.*
FROM (
select trim(regexp_substr(str,'[^;]+', 1, level)) as str1
from (
SELECT ('Cogent Communications Poland Sp. z.o.o. - 100000Mbps;E-point - 100000Mbps; T-Mobile - 100000Mbps; Net Friends - 100000Mbps' ) as Str
FROM dual
)
connect by regexp_substr(str, '[^;]+', 1, level) is not null
) inserted
CROSS APPLY(
select trim(regexp_substr(str1,'[^-]+', 1, 1)) as key,
trim(regexp_substr(str1,'[^-]+', 1, 2)) as value
from dual
) arraylist
Here's one way to handle multi-character delimited strings. First CTE just sets up data. tbl_case is the result of splitting on the semi-colon. Lastly split on the delimiter of ' - ' to get your name-value pairs.
Note a couple of things. The values are not realistic, but serve to differentiate the differing rows. The regex form of
'(.*?)(;|$)'
handles NULL elements properly, should they occur. See this post for more info on that.