Question on the wide card "%" in the "like" statement

Question

Question on the wide card "%" in the "like" statement

home2end

SSC Journeyman

Points: 92
More actions
September 14, 2009 at 8:34 pm

#215247

Hi, there,
I'm working on a table T which has a column C of type nvarchar(max)
I tried to use the wide card % to select all the records that contains 'AB20' in column C by using the following statement
select * from T where C like '%AB20%'
However, I noticed that the query results missed quite a number of records that have 'AB20' in C.
I then did a little test on one of the records that are missed by the statement above. I changed statement to
select * from T where C like '%20%' and select * from T where C like '%AB%'
Both times, that missing record shows up in the query result.
Then I tried select * from T where C like '%B2%'. This time, the record is not selected.
Any clue?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I was thinking there was some limit like 4000 characters on the LIKE operator but I can't find anything that says that. Going out on a limb, you might try adding N to the begining of the search text.. I don't know if it will have any effect, but I just realized you have a nvarchar(max) for the type..

CEWII

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Something i faced yesterday, though this may have nothing to do with your issue. I had a space in between a string and on checking the space turned out to be some sort of a special character. The data-type was nvarchar(100).

"Keep Trying"

HowardW One Orange Chip Points: 29109 More actions · Answer 3

Try the following for the values giving trouble and post the results:

select charindex('AB20',C), charindex('AB',C),charindex('20',C),C from T

It should indicate whether there's another character between the 'AB' and the '20'

Apurva SSCrazy Points: 2412 More actions · Answer 4

@Chirag and @home2end

I found this on msdn.

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the SQL-92 standard. ASCII LIKE is compatible with earlier versions of SQL Server.

Here is a series of examples that show the differences in rows returned between ASCII and Unicode LIKE pattern matching:

-- ASCII pattern matching with char column

CREATE TABLE t (col1 char(30))

INSERT INTO t VALUES ('Robert King')

SELECT *

FROM t

WHERE col1 LIKE '% King' -- returns 1 row

-- Unicode pattern matching with nchar column

CREATE TABLE t (col1 nchar(30))

INSERT INTO t VALUES ('Robert King')

SELECT *

FROM t

WHERE col1 LIKE '% King' -- no rows returned

-- Unicode pattern matching with nchar column and RTRIM

CREATE TABLE t (col1 nchar (30))

INSERT INTO t VALUES ('Robert King')

SELECT *

FROM t

WHERE RTRIM(col1) LIKE '% King' -- returns 1 row

http://msdn.microsoft.com/en-us/library/aa933232%28SQL.80%29.aspx

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

I saw this one too, however, your highlighted portion is only relevant when using like in the '%sddfg' scenarion and is not relevant in the '%sddfg%' scenario, good article though.. The UNICODE part is relevant though.

CEWII

home2end SSC Journeyman Points: 92 More actions · Answer 6

Thanks you all for the help, especially the tips from HowardW and Prurva. I finally found an err in my code. Sorry, not the first time for me though and probably not the last time either. After fixing the err, I got most of (if not all of) the missing records.