如何在SQL Server中查找数值和逗号分隔的数值。 [英] How to find numeric values and comma separated numeric values in 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屋！