-1

I have simple select query:

Select * from ABC
where title like N'%[text] xxxxx%'   => return nothing.

Select * from ABC
where title like N'%xxxxx%'  => correct record   '[text] xxxx'

My table has some records with content contains [xxx] ..... and i have to compare them with other content from source outside the database, and the content from the source also may contains square bracket.

e.g: i could have thousands of titles from othersource to compare:

  • [text] content
  • content

2 formats, just don't know how to compare with the title in Database because i dont know beforehand its structure.

How can I pass in a content that possible contains a square bracket and still get the correct result ?

nam vo
  • 3,271
  • 12
  • 49
  • 76
  • '[text] xxxx' has 4 'x', obviously it doesn't match N'%[text] xxxxx%' which has 5 'x', is it right? – Hieu Vo May 28 '14 at 04:13

3 Answers3

1

Searching with special characters

SELECT * 
FROM ABC
WHERE title LIKE N'%\[text] xxxxx%' ESCAPE '\'
Community
  • 1
  • 1
potashin
  • 44,205
  • 11
  • 83
  • 107
1

You need to use it like below

Select * from ABC 
where title like N'%[[]text] xxxxx%'

check this related SO thread here How can I escape square brackets in a LIKE clause?

Community
  • 1
  • 1
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • yes i know these, but the text is unknown, it could be anything, and the bracket could be in any where in the like clause. – nam vo May 28 '14 at 04:03
  • 1
    @namvo, yes it could be anywhere but the point here is it needs to be escaped likewise. – Rahul May 28 '14 at 04:04
1

You can use [] to surround a special character (or range). So if you want your search cantains any of the brackets, just use "[[]" instead of "[" and "[]]" instead of "]" in your where clause.

Select * from ABC
where title like N'%[[]text[]] xxxxx%'
Hamid Reza
  • 477
  • 1
  • 4
  • 11