SQL Server搜索时使用like,而忽略空格 [英] SQL Server search using like while ignoring blank spaces
问题描述
我在数据库中有一个电话
列,并且记录的右侧包含不需要的空格。我尝试使用修剪和替换,但是没有返回正确的结果。
I have a phone
column in the database, and the records contain unwanted spaces on the right. I tried to use trim and replace, but it didn't return the correct results.
如果我使用
phone like '%2581254%'
它返回
customerid
-----------
33470
33472
33473
33474
但我只需要在开头使用百分号或通配符,我只想匹配左侧。
but I need use percent sign or wild card in the beginning only, I want to match the left side only.
因此,如果我这样使用
phone like '%2581254'
我什么也没得到,因为上面有空格正确的!
I get nothing, because of the spaces on the right!
所以我尝试使用修剪和替换,但只得到一个结果
So I tried to use trim and replace, and I get one result only
LTRIM(RTRIM(phone)) LIKE '%2581254'
返回
customerid
-----------
33474
请注意,这四个ID具有相同的电话号码!
Note that these four ids have same phone number!
表数据
customerid phone
-------------------------------------
33470 96506217601532388254
33472 96506217601532388254
33473 96506217601532388254
33474 96506217601532388254
33475 966508307940
我为测试建议添加了许多数字
I added many number for test propose
php函数使用了最后一个7位数字进行比较。
The php function takes last 7 digits and compare them.
例如
01532388254 will be 2581254
,我要搜索其电话号码
2581254
and I want to search for all users that has this 7 digits in their phone number 2581254
我不知道问题出在哪里!
I can't figure out where's the problem!
它应该返回4个id而不是1个
It should return 4 ids instead of 1 id
推荐答案
鉴于示例数据,我怀疑您的数据中有控制字符。例如char(13),char(10)
Given the sample data, I suspect you have control characters in your data. For example char(13), char(10)
要确认这一点,只需运行以下
Select customerid,phone
From YourTable
Where CharIndex(CHAR(0),[phone])+CharIndex(CHAR(1),[phone])+CharIndex(CHAR(2),[phone])+CharIndex(CHAR(3),[phone])
+CharIndex(CHAR(4),[phone])+CharIndex(CHAR(5),[phone])+CharIndex(CHAR(6),[phone])+CharIndex(CHAR(7),[phone])
+CharIndex(CHAR(8),[phone])+CharIndex(CHAR(9),[phone])+CharIndex(CHAR(10),[phone])+CharIndex(CHAR(11),[phone])
+CharIndex(CHAR(12),[phone])+CharIndex(CHAR(13),[phone])+CharIndex(CHAR(14),[phone])+CharIndex(CHAR(15),[phone])
+CharIndex(CHAR(16),[phone])+CharIndex(CHAR(17),[phone])+CharIndex(CHAR(18),[phone])+CharIndex(CHAR(19),[phone])
+CharIndex(CHAR(20),[phone])+CharIndex(CHAR(21),[phone])+CharIndex(CHAR(22),[phone])+CharIndex(CHAR(23),[phone])
+CharIndex(CHAR(24),[phone])+CharIndex(CHAR(25),[phone])+CharIndex(CHAR(26),[phone])+CharIndex(CHAR(27),[phone])
+CharIndex(CHAR(28),[phone])+CharIndex(CHAR(29),[phone])+CharIndex(CHAR(30),[phone])+CharIndex(CHAR(31),[phone])
+CharIndex(CHAR(127),[phone]) >0
如果测试结果为阳性
可以使用以下UDF通过更新从数据中剥离控制字符
The following UDF can be used to strip the control characters from your data via an update
Update YourTable Set Phone=[dbo].[udf-Str-Strip-Control](Phone)
感兴趣的UDF
CREATE FUNCTION [dbo].[udf-Str-Strip-Control](@S varchar(max))
Returns varchar(max)
Begin
;with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(C) As (Select Top (32) Char(Row_Number() over (Order By (Select NULL))-1) From cte1 a,cte1 b)
Select @S = Replace(@S,C,' ')
From cte2
Return LTrim(RTrim(Replace(Replace(Replace(@S,' ','><'),'<>',''),'><',' ')))
End
--Select [dbo].[udf-Str-Strip-Control]('Michael '+char(13)+char(10)+'LastName') --Returns: Michael LastName
按照承诺(由Bill推动),以下是对UDF的一些评论。
As promised (and nudged by Bill), the following is a little commentary on the UDF.
- 我们传递一个要剥离的控制字符的字符串
- 我们创建一个由0至31个ASCII字符组成的临时表/ li>
- 然后,我们为
计数表中的每个字符运行全局搜索和替换。找到的每个字符都将替换为一个空格 - 最后的字符串被去除了重复的空格(几周前
Gordon演示了一个小技巧-没有原始的
链接)
这篇关于SQL Server搜索时使用like,而忽略空格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!