Menu
Using SQL Server 2008. I have a table with the following column:
sampleData (nvarchar(max)) The value for this column in some of these rows are lists formatted as follows:
["value1","value2","value3"] I'm trying to write a simple query that will return all rows with lists formatted like this, by just detecting the opening bracket.
SELECT * from sampleTable where sampleData like '[%' The above query doesn't work because '[' is a special character, and I can't for the life of me figure out how to escape the bracket so my query does what I want.
Thanks for any suggestions!
411
Regardless of following a naming convention that avoids using reserved words, Microsoft does add new reserved words. Using brackets allows your code to be upgraded to a new SQL Server version, without first needing to edit Microsoft's newly reserved words out of your client code.
SQL: Finding square brackets using LIKE in SQL Server T-SQL The trick is that to find the opening bracket, you need to enclose it in a pair of square brackets. But you can just find the closing one directly. That returns no rows. That returns rows 1, 2, and 4 as expected.
To escape square brackets in LIKE you can use another square bracket to escape the original square bracket or use a custom escape character using the ESCAPE keyword in LIKE clause.
On SQL Server and MS Access, square brackets have a special meaning when used in a query filter. The square brackets are used to specify a set or range of characters, as in "[A-Z]" which would match any single character from 'A' to 'Z'.
... like '[[]%' You use [ ] to surround a special character (or range)
See the section "Using Wildcard Characters As Literals" in SQL Server LIKE
Edit, 24 Nov 2011
Note: You don't need to escape the closing bracket...
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
