I have a text like:
我有一个文字像:
FHUGBGH(4545496),DFHGUFG(5464564),GFUEIFG(456)
I want to remove all non-digit characters and concatenate the resulting numbers with a commas. The output should be:
我想删除所有非数字字符并用逗号连接结果数字。输出应该是:
4545496,5464564,456
No brackets or anything, only the numeric part separated with commas.
没有括号或任何东西,只有用逗号分隔的数字部分。
I tried using replace
, regexp_replace
etc. but was not successful.
我尝试使用replace,regexp_replace等,但没有成功。
2 个解决方案
#1
0
PL/pgSQL or just plain SQL, the expression is the same. Using regexp_replace()
:
PL / pgSQL或只是普通的SQL,表达式是一样的。使用regexp_replace():
If it's guaranteed that there is already exactly one comma between each string of digits and nowhere else:
如果保证每个数字串之间只有一个逗号,那么其他地方:
regexp_replace (txt, '[^\d,]', '', 'g')
If we can't rely on that:
如果我们不能依赖于此:
trim(regexp_replace (txt, '\D+', ',', 'g'), ',')
\d
.. class shorthand for \[\[:digit:\]\]
- only digits\D
.. the counterpart: [^[:digit:]]
- everything except digits'g'
.. the forth parameter is required to replace "globally" and not just the first match.
\ d .. \ [\ [:digit:\] \]的简写 - 只有数字\ D ..对应:[^ [:digit:]] - 除数字'g'以外的所有内容..第四个参数是必需的取代“全球”而不仅仅是第一场比赛。
Demo:
演示:
SELECT regexp_replace (txt, '[^\d,]', '', 'g') AS nr1
, trim(regexp_replace (txt, '\D+', ',', 'g'), ',') AS nr2
FROM (VALUES ('FHUGBGH(4545496),DFHGUFG(5464564),GFUEIFG(456)')) t(txt);
#2
1
Use this:
用这个:
declare
l_text varchar2 (100) := 'FHUGBGH(4545496),DFHGUFG(5464564),GFUEIFG(456)';
begin
dbms_output.put_line (regexp_replace (l_text, '[^,0-9]', ''));
end;
PL/SQL block executed
执行PL / SQL块
4545496,5464564,456
4545496,5464564,456
Edit, I just noticed the Postgres flag. You probably meant PQSQL not PLSQL which is Oracle RDBMS. I hope it works similar.
编辑,我刚注意到Postgres旗帜。你可能意味着PQSQL而不是PLSQL,它是Oracle RDBMS。我希望它的工作原理类似。
#1
0
PL/pgSQL or just plain SQL, the expression is the same. Using regexp_replace()
:
PL / pgSQL或只是普通的SQL,表达式是一样的。使用regexp_replace():
If it's guaranteed that there is already exactly one comma between each string of digits and nowhere else:
如果保证每个数字串之间只有一个逗号,那么其他地方:
regexp_replace (txt, '[^\d,]', '', 'g')
If we can't rely on that:
如果我们不能依赖于此:
trim(regexp_replace (txt, '\D+', ',', 'g'), ',')
\d
.. class shorthand for \[\[:digit:\]\]
- only digits\D
.. the counterpart: [^[:digit:]]
- everything except digits'g'
.. the forth parameter is required to replace "globally" and not just the first match.
\ d .. \ [\ [:digit:\] \]的简写 - 只有数字\ D ..对应:[^ [:digit:]] - 除数字'g'以外的所有内容..第四个参数是必需的取代“全球”而不仅仅是第一场比赛。
Demo:
演示:
SELECT regexp_replace (txt, '[^\d,]', '', 'g') AS nr1
, trim(regexp_replace (txt, '\D+', ',', 'g'), ',') AS nr2
FROM (VALUES ('FHUGBGH(4545496),DFHGUFG(5464564),GFUEIFG(456)')) t(txt);
#2
1
Use this:
用这个:
declare
l_text varchar2 (100) := 'FHUGBGH(4545496),DFHGUFG(5464564),GFUEIFG(456)';
begin
dbms_output.put_line (regexp_replace (l_text, '[^,0-9]', ''));
end;
PL/SQL block executed
执行PL / SQL块
4545496,5464564,456
4545496,5464564,456
Edit, I just noticed the Postgres flag. You probably meant PQSQL not PLSQL which is Oracle RDBMS. I hope it works similar.
编辑,我刚注意到Postgres旗帜。你可能意味着PQSQL而不是PLSQL,它是Oracle RDBMS。我希望它的工作原理类似。