How to Search for Literal Square Brackets in SQL Server Stored Procedure Definitions?

Search for text containing square brackets in SQL Server stored procedures

You want to find all stored procedures in the database that contain a specific text string, including cases where the string is enclosed in square brackets, but are experiencing some difficulty. You used the following query:

<code class="language-sql">SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
WHERE m.definition Like '%[ABD]%';</code>

However, this query returns incorrect results because the SQL LIKE operator interprets square brackets as wildcard characters rather than literal characters.

Solution:

To search for literal square brackets in a stored procedure definition, you need to escape them in the query. This can be achieved using the ESCAPE clause:

<code class="language-sql">...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'</code>

The

ESCAPE clause tells SQL Server that the following characters should not be interpreted as wildcards but as literal characters. In this example, the backslash character () is used as the escape character.

By using escaped square brackets, your query will now accurately identify and display stored procedures that contain the string "[ABD]" in their definition.

The above is the detailed content of How to Search for Literal Square Brackets in SQL Server Stored Procedure Definitions?. For more information, please follow other related articles on the PHP Chinese website!