I want to find all instances in a table where a string field cannot be cast as a number. Is there a way to do like a "try" in t-sql so that I can get all the id's where the cast failed and delete the value?
我想在表中找到不能将字符串字段强制转换为数字的所有实例。有没有办法像t-sql中的“try”那样做,以便我可以获得转换失败的所有id并删除值?
2 个解决方案
#1
select *
from yourTable
Where ISNUMERIC(yourField) = 0
#2
I'm not a t-SQL user so please take this as a disclaimer. Sorry if it's not the perfect answer to your question but I use MySQL which can use Regular Expressions. Now depending upon what you actually aim to do.. this might not be your final solution but here is a quickie example of a query I could run:
我不是t-SQL用户,所以请将此作为免责声明。对不起,如果它不是你的问题的完美答案,但我使用MySQL可以使用正则表达式。现在取决于你实际的目标......这可能不是你的最终解决方案,但这里是我可以运行的查询的简单示例:
SELECT * FROM table WHERE columnName REGEXP '^[0-9]+$'
But again not sure if t-sql can do this but from this link: http://www.sqlteam.com/article/regular-expressions-in-t-sql it seems like it does. Hopefully this is atleast a start to your solution.
但再次不确定t-sql是否可以做到这一点,但从这个链接:http://www.sqlteam.com/article/regular-expressions-in-t-sql它似乎它。希望这至少是您解决方案的开始。
Again, not sure completely what your end use is.. but If I wanted only numbers I would use some sort of cleansing before loading data into the database rather than cleaning up afterwards...I always find this the easier route. (You may have started with the data pre-existing though)
再次,不完全确定你的最终用途是什么..但如果我只想要数字,我会在将数据加载到数据库之前使用某种清理而不是事后清理...我总是觉得这是更容易的路线。 (您可能已经开始使用预先存在的数据)
Hope this helps!
希望这可以帮助!
#1
select *
from yourTable
Where ISNUMERIC(yourField) = 0
#2
I'm not a t-SQL user so please take this as a disclaimer. Sorry if it's not the perfect answer to your question but I use MySQL which can use Regular Expressions. Now depending upon what you actually aim to do.. this might not be your final solution but here is a quickie example of a query I could run:
我不是t-SQL用户,所以请将此作为免责声明。对不起,如果它不是你的问题的完美答案,但我使用MySQL可以使用正则表达式。现在取决于你实际的目标......这可能不是你的最终解决方案,但这里是我可以运行的查询的简单示例:
SELECT * FROM table WHERE columnName REGEXP '^[0-9]+$'
But again not sure if t-sql can do this but from this link: http://www.sqlteam.com/article/regular-expressions-in-t-sql it seems like it does. Hopefully this is atleast a start to your solution.
但再次不确定t-sql是否可以做到这一点,但从这个链接:http://www.sqlteam.com/article/regular-expressions-in-t-sql它似乎它。希望这至少是您解决方案的开始。
Again, not sure completely what your end use is.. but If I wanted only numbers I would use some sort of cleansing before loading data into the database rather than cleaning up afterwards...I always find this the easier route. (You may have started with the data pre-existing though)
再次,不完全确定你的最终用途是什么..但如果我只想要数字,我会在将数据加载到数据库之前使用某种清理而不是事后清理...我总是觉得这是更容易的路线。 (您可能已经开始使用预先存在的数据)
Hope this helps!
希望这可以帮助!