在地址字段中的房屋编号前插入逗号 [英] 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 条记录要解决.

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

提前致谢丹尼斯

推荐答案

Db2 for i 7.2 支持 REGEXP_REPLACE 函数

Db2 for i 7.2 supports the REGEXP_REPLACE function

https://www.ibm.com/support/knowledgecenter/en/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屋！