MS SQL 2005 compare field containing square parenthesis
I am using MS SQL Server 2005 (9.0.4035) and trying to find rows that contain the same data in a nvarchar(4000) field. The field contains xml that has both opening and closing square parentheses.
Here is sample data:
Using the ‘like’ operator I expected to get 3 matching pairs, but my problem is that row 5 and 6 do not match each other, I only get back that rows 1 & 2 match, and 3 & 4 match.
I know MS SQL 2005 added regular expression support in queries but I did not expect them to evaluate field data as a regular expression, which I think it is doing. Is there a mode that I need to enable to get the proper results?
Any help appreciated,
Edit: Added sql statement used:
Select t1.DataID, t2.DataID From TestTable t1, TestTable t2
Where t1.DataID <> t2.DataID
and t1.Data like t2.Data
Using ‘=’ operator works, but escaping the ‘[‘ does not.
One Solution collect form web for “MS SQL 2005 compare field containing square parenthesis”
Change your query to use = instead of LIKE and you’ll get the results that you expect. SQL 2005 T-SQL won’t do regex – you’d need to use CLR functions for that – but the LIKE statment does do pattern matching. ‘[‘ and ‘]’ are reserved for the pattern matching in a like statment, and you’d have to escape them out if you intended for them to be equality matches.
See http://msdn.microsoft.com/en-us/library/ms179859.aspx for info on the LIKE statement.
Either of the 2 queries below solved the problem in my tests…
--using equals operator... Select t1.DataID, t2.DataID From TestTable t1, TestTable t2 Where t1.DataID <> t2.DataID and t1.Data = t2.Data --using replace to add an escape character. Select t1.DataID, t2.DataID From TestTable t1, TestTable t2 Where t1.DataID <> t2.DataID and t1.Data like REPLACE(t2.Data, '[', '\[') escape '\'