SQL Server Search Using Like While Ignoring Blank Spaces

SQL Server search using like while ignoring blank spaces

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

As promised (and nudged by Bill), the following is a little commentary on the UDF.

1. We pass a string that we want stripped of Control Characters
2. We create an ad-hoc tally table of ascii characters 0 - 31
3. We then run a global search-and-replace for each character in the
   tally-table. Each character found will be replaced with a space
4. The final string is stripped of repeating spaces (a little trick
   Gordon demonstrated several weeks ago - don't have the original
   link)

Query that ignore the spaces

SELECT * FROM mytable 
    WHERE REPLACE(username, ' ', '') = REPLACE("John Bob Jones", ' ', '')

SQL - string comparison ignores space

Turns out that the Name column was NVARCHAR (even though it contained ASCII characters only) and NVARCHAR behaves differently than VARCHAR:

SELECT CASE WHEN N'abc ' LIKE 'abc' THEN 1 ELSE 0 END

Returns 0, ditto for column instead of literal. The following does return 1 still:

SELECT CASE WHEN N'abc ' = 'abc' THEN 1 ELSE 0 END

So = and LIKE work differently, another peculiar difference.

Like operator and Trailing spaces in SQL Server

This is a case of reading the documentation, it's very explicitly stated here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.

Edit: According to your comments, you seem to be looking for a way to use like while ignoring trailing spaces. Use something like this: field like rtrim('abc '). It will still use indexes because rtrim() is a scalar operand and it's evaluated before the lookup phase.

SQL string comparison -how to ignore blank spaces

You can use trim on the column.

where trim(product_type) is null

The above is not DBMS-independent, since Sybase does not provide the trim function.
However, the below approach will work both in Sybase and Oracle:

where rtrim(ltrim(product_type)) is null

Ignoring spaces between words - an SQL question

Try this:

SELECT * FROM table WHERE lower(NAME) like 'john%doe%'

use like with wildcards (e.g. %) to get around the spaces and the lower (orlcase) to be case insensitive.

EDIT:

As the commenters pointed out, there are two shortcomings within this solution.

First: you will select "johnny doe" or worse "john Eldoe", or worse, "john Macadoelt" with this query, so you'll need extra filtering on the application side.

Second: using a function can lead to table scans instead of index scans. This may be avoided, if your dbms supports function based indexes. See this Oracle example

Is there any built-in Function in SQL to ignore white space when comparing strings

Use REPLACE():

REPLACE(TDC_TREE_FAMILY_CLASSIFICATION + ', ' + TDC_TREE_CLASSIFICATION + ', ' + TDC_TREE_SUB_CLASSIFICATION + ' , ' + 'A', ' ', '') IN
               (SELECT REPLACE(DPC_Level1 + ', ' + DPC_Level2 + ', ' + DPC_Level3 + ', ' + DPC_Status, ' ', '')
                 FROM DATA_PACK_CATEGORIES )

You can use the trim functions if you only care about spaces at the beginning/end of a string (actually, spaces at the end are usually ignored ignored anyway).

You can extend the above for additional characters by nesting REPLACE() calls.

Why the SQL Server ignore the empty space at the end automatically?

SQL Server is following the ANSI/ISO standard for string comparison.

The article How SQL Server Compares Strings with Trailing Spaces explains this in detail.

SQL Server follows the ANSI/ISO SQL-92 specification... on how to
compare strings with spaces. The ANSI standard requires padding for
the character strings used in comparisons so that their lengths match
before comparing them. The padding directly affects the semantics of
WHERE and HAVING clause predicates and other Transact-SQL string
comparisons. For example, Transact-SQL considers the strings 'abc' and
'abc ' to be equivalent for most comparison operations.

Also, as explained in the article, if you compare with LIKE you do not get this behaviour.

---

Related Topics