如何在SQL Server中查找数值和逗号分隔的数值。 [英] How to find numeric values and comma separated numeric values in SQL server.
本文介绍了如何在SQL Server中查找数值和逗号分隔的数值。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我可以在varchar类型列中包含值12345或12345,45678,890765
从PlayerAnswer中选择*其中答案是数字或逗号分隔的数值。
我尝试过:
I can have values 12345 or 12345,45678,890765 inside a varchar type column
Select * from PlayerAnswer where answer is numeric or comma separated numeric values.
What I have tried:
select * from PlayerAnswer where ISNUMERIC(Answer)=1 and Answer != null
但我得到了不正确的结果。
but i am getting incorrect results.
推荐答案
ISNUMERIC仅在整个字符串可以转换为单个数字时有效:逗号分隔的数据不会返回1,因为它无法强制转换为一个有效的数字格式。
老实说,你是在SQL中做这个的失败者 - 虽然它可以完成,但它会很复杂,而且很难维护 - 你会在你的演示代码中做得更好,而不是试图强制它进入数据库函数。
如果你坚持做SQL,那么这可能会帮助你已启动:将列中的逗号分隔数据转换为行选择 [ ^ ] - 但期望它是一个混乱的过程,SQL(充其量)字符串处理不佳。
ISNUMERIC only works if the whole string can be converted into a single number: comma delimited data will not return 1 as it can't be coerced into a valid numeric format.
To be honest, you are on a loser doing this in SQL - while it can be done, it's going to be complicated, and difficult to maintain - you would be much better off doing this in your presentation code rather that trying to force it into a database function.
If you insist on doing it SQL, then this may help you get started: Converting comma separated data in a column to rows for selection[^] - but expect it to be a messy process, SQL has (at best) poor string handling.
这篇关于如何在SQL Server中查找数值和逗号分隔的数值。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文