Ahmadz Issa Ahmadz Issa -4 years ago 138
SQL Question

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.

If I use

phone like '%2581254%'


it returns

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.

So if I use it like this

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'


returns

customerid
-----------
33474


Note that these four ids have same phone number!

Table data

customerid phone
-------------------------------------
33470 96506217601532388254
33472 96506217601532388254
33473 96506217601532388254
33474 96506217601532388254
33475 966508307940


I added many number for test propose

The php function takes last 7 digits and compare them.

For example

01532388254 will be 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!

It should return 4 ids instead of 1 id

Answer Source

Given the sample data, I suspect you have control characters in your data. For example char(13), char(10)

To confirm this, just run the following

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

If the Test Results are Positive

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)

The UDF if Interested

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download