CODEWITHSUNDEEP
sqlsql-servert-sql
Davit Lekishvili
Davit Lekishvili

Reputation: 11

SQL Server not finding the string with brackets

I have one record in table Payment(id int, message nvarchar(500)), I have inserted row with id=1 and message="Wrong param [abonentCode]:".

When I try to select the record with message "Wrong param [abonentCode]:" sql server returns 0 rows.

I have tried these statements and none of them works:

SELECT * FROM Payment WHERE message = N'Wrong param [abonentCode]:'
SELECT * FROM Payment WHERE message = 'Wrong param [abonentCode]:'

Upvotes: 1

Views: 639

Answers (3)

Ziyad PK
Ziyad PK

Reputation: 567

Works fine when executed in SQL 2008 Or 2014.

See SQL Fiddle for Demo

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

Let's have a look to binary data obtained from inserted string you've provided in comments:

0x57726F6E6720706172616D205B61626F6E656E74436F64655D3A20636F6D

Casting it back to varchar gives us:

Wrong param [abonentCode]: com

But you're trying to find it as 

WHERE message = 'Wrong param [abonentCode]:'

Notice strings mismatch (extra com at the end of inserted string) - so you can't use equality operator here.

Possible options:

  1. Correct string you're using in where condition in order to find by strong equality;

  2. Use substring: 

select *  from Payment where left(message, 26) = 'Wrong param [abonentCode]:'
  1. Use like operator (notice in this case you have to add % to the pattern to denote any number of any characters as well as put opening square bracket into enclosing brackets to escape it since pair of square brackets has special meaning in like operator patterns):

select *  from Payment where message like 'Wrong param [[]abonentCode]:%'

Upvotes: 4

Bjorn Karrlander
Bjorn Karrlander

Reputation: 31

You must escape the opening bracket (surround it with []).

I would use:

SELECT * FROM Payment WHERE message LIKE 'Wrong param [[]abonentCode]:%'

(Also note the possible need of a trailing %)

Sorry, above should be LIKE, not =

Upvotes: 0

Related Questions