英文:
Replace delimiter between non-digits - Redshift
问题
我正在尝试执行一个查询，只在点（.）位于非数字之间的位置替换为逗号（,）。
表：
full_name
LISADOR. COMPRIMIDO (16UN)
ALOPURINOL 1.0MG. COMPRIMIDO (30UN)
RILAN 40MG/ML. SOLUCAO NASAL (1UN DE 13ML)
DORENE 1.0MG. CAPSULA (30UN)
我期望得到以下结果：
full_name_new
LISADOR, COMPRIMIDO (16UN)
ALOPURINOL 1.0MG, COMPRIMIDO (30UN)
RILAN 40MG/ML, SOLUCAO NASAL (1UN DE 13ML)
DORENE 1.0MG, CAPSULA (30UN)
为了实现这个目标，我尝试了以下正则表达式：
REGEXP_REPLACE(full_name, '(.*\D)\.(\D.*)', ',') as full_name_new
我在Postgre中测试了这个正则表达式，它有效，但在Redshift中它返回与输入相同的列，没有进行更改。
有人知道在Redshift中需要做哪些更改才能得到这个结果吗？
英文:
I am trying to execute a query to replace dot (.) with a comma (,) only in positions where the dot is between non-digits.
Table:
full_name
LISADOR. COMPRIMIDO (16UN)
ALOPURINOL 1.0MG. COMPRIMIDO (30UN)
RILAN 40MG/ML. SOLUCAO NASAL (1UN DE 13ML)
DORENE 1.0MG. CAPSULA (30UN)
I am expecting this result:
full_name_new
LISADOR, COMPRIMIDO (16UN)
ALOPURINOL 1.0MG, COMPRIMIDO (30UN)
RILAN 40MG/ML, SOLUCAO NASAL (1UN DE 13ML)
DORENE 1.0MG, CAPSULA (30UN)
To do this, I'm trying this regex:
REGEXP_REPLACE(full_name, '(.*\D)\.(\D.*)', ',') as full_name_new
I tested this regex in Postgre and it worked, but in Redshift it returned the same column as the input, without the change.
Does anyone know what changes are needed in Redshift to get this result?
答案1
得分: 1
这应该有效：
REGEXP_REPLACE(full_name, ''(.*[^0-9])\\.([^0-9].*)'', ''\,\'') as full_name_new
你的代码行不起作用，因为你没有对 \\D 进行双重转义。请参考文档。可能还需要使用 p 标志，如REGEXP_REPLACE所指定的。
英文:
This should work:
REGEXP_REPLACE(full_name, '(.*[^0-9])\\.([^0-9].*)', ',') as full_name_new
Your line didn't work because you didn't double escape \\D. See documentation. The p flag is likely also needed as REGEXP_REPLACE specifies.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论