英文:
MySQL CAST loses decimal digits when source is result from a regexp, but works fine on the same string when its hardcoded
问题
我在这里得到了完全令人困惑的结果... 我将其简化为以下内容:
select
regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') raw_txt,
cast('56.948959' as decimal(10,8)) just_cast,
case when regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') = '56.948959' then 'yisss' else 'nope' end text_comparison,
cast(regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') as decimal(10,8)) full_decimal,
cast(regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') as double) full_double
而我得到的结果是:
raw_txt: 56.948959
just_cast: 56.94895900
text_comparison: yisss
full_decimal: 56.00000000
full_double: 56.948959
在full_decimal
情况下,为什么没有小数位数?为什么在对简单字符串运行时转换正常工作(just_cast
),但是当完全相同的字符串来自正则表达式匹配时(请参见text_comparison
),转换不起作用?然而,如果我们转换为double
,它就有效了?这里发生了什么?
MySQL 版本:8.0.21
英文:
I'm getting completely perplexing results here... I boiled it down to this:
select
regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') raw_txt,
cast('56.948959' as decimal(10,8)) just_cast,
case when regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') = '56.948959' then 'yisss' else 'nope' end text_comparison,
cast(regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') as decimal(10,8)) full_decimal,
cast(regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') as double) full_double
And the results that I get are:
raw_txt: 56.948959
just_cast: 56.94895900
text_comparison: yisss
full_decimal: 56.00000000
full_double: 56.948959
Where are the decimal digits in the full_decimal
case? Why does the cast work fine when run on a simple string (just_cast
), but not when the exact same string (see text_comparison
) comes from a regexp match? And yet it works if we cast to a double
instead? What is going on here?
MySQL version: 8.0.21
答案1
得分: 1
这被报告为一个错误。
如果你先将其转换为字符,然后再转换为十进制,转换就会成功。
例如:
select cast(cast(regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') as char(100)) as decimal(10,8)) full_decimal
英文:
This is reported as a bug.
If you cast first to char and then re-cast to decimal the cast works.
For example:
select cast(cast(regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.]+') as char(100)) as decimal(10,8)) full_decimal
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论