September 14, 2009 at 8:34 pm
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.
September 14, 2009 at 9:02 pm
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
September 14, 2009 at 11:28 pm
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"
September 15, 2009 at 3:18 am
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'
September 15, 2009 at 10:34 am
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
September 15, 2009 at 10:40 am
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
September 15, 2009 at 3:43 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply