SQL Server - 将字段内容仅筛选为数字 [英] SQL Server - Filter field contents to numbers only

问题描述

我如何复制一个字段的值，但只复制它的数字？

How can I copy the value of a field, but only its numbers?

我正在为全文搜索创建一个计算列，并且我想复制这些值从我的电话号码字段（这是varchar）进入它，但不是与他们的格式 - 数字只。在我的计算列公式中可以做到这一点的命令是什么？

I am creating a computed column for fulltext search, and I want to copy the values from my Phone Number fields (which are varchar) into it, but not with their formatting - numbers only. What is the command that would do this in my computed column formula?

谢谢！

Thank you!

推荐答案

您将不得不编写用户定义的函数来执行此操作。

You are going to have to write a user defined function to do this. There are several ways to do this, here is one that I found with some quick Googling.

CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
  DECLARE @pos INT
  SET @Pos = PATINDEX('%[^0-9]%',@Input)
  WHILE @Pos > 0
   BEGIN
    SET @Input = STUFF(@Input,@pos,1,'')
    SET @Pos = PATINDEX('%[^0-9]%',@Input)
   END
  RETURN @Input
END

警告：我不会把它放在大表上的WHERE条件中，或者在返回数以百万计的行的SELECT中，但它会起作用。

Warning: I wouldn't put this in a WHERE condition on a large table, or in a SELECT that returns millions of rows, but it will work.

最后，您可能会更好地在应用的用户界面中删除非数字字符，而不是在数据库代码中。

Ultimately you are probably better stripping the non-numeric characters out in the UI of your app than in DB code.

这篇关于SQL Server - 将字段内容仅筛选为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！