在房号前的地址字段中插入逗号 [英] Insert a comma in address field before number of house

问题描述

我有一个表，其中有很多地址字段，街道名称和门牌号之间没有逗号
。
类似的东西：

I have a table with a lot of address field that have no comma between street name and house number. Something like:

"VIA MILANO 123"
"VIA MILANO    A123"
"VIA 11 MILANO      AA123"

我需要的是一个在
之前插入逗号的SQL最后一个包含数字的组...

What I need is a SQL that insert the comma before the last group that contain numbers... like:

"VIA MILANO, 123"
"VIA MILANO,    A123"
"VIA 11 MILANO,      AA123"

我在网上发现了一些东西，但有很多想法在IBM DB上不起作用。

I found something on net but a lot of thinks is not work on IBM DB".

有人可以帮我吗？
我要解决的文件中有100000多个recs。

Can anybody help me please ? I have more than 100000 recs in the file to solve.

预先感谢
Denis

Thanks in advance Denis

推荐答案

Db2 for i 7.2支持REGEXP_REPLACE函数

Db2 for i 7.2 supports the REGEXP_REPLACE function

https://www.ibm.com/support/knowledgecenter/zh-CN/ssw_ibm_i_72/db2/rbafzscaregexp_replace.htm

尝试一下

SELECT c, REGEXP_REPLACE(c,'(\s+\S*\d+\s*$)',',\1') 
FROM TABLE(
    VALUES
     ('VIA MILANO 123     ')
    ,('VIA MILANO A123    ')
    ,('VIA 11 MILANO AA123')
) AS t(c)

返回

 C                      2
 -------------------    --------------------
 VIA MILANO 123         VIA MILANO, 123
 VIA MILANO A123        VIA MILANO, A123
 VIA 11 MILANO AA123    VIA 11 MILANO, AA123

正则表达式提供了一种强大而灵活的方式来操作字符串，值得学习。

Regular Expressions provide a powerful and flexible way to manipulate strings , and it is worth learning about them.

https://www.ibm.com/support/knowledgecenter/en /ssw_ibm_i_72/db2/rbafzregexp_like.htm#rbafzregexp_like__regexp_likecontrol

这篇关于在房号前的地址字段中插入逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！