a4194304 a4194304 - 9 months ago 53
SQL Question

SQL Use LIKE predicate to match empty string

I want to find which SP a column named

is modified by UPDATE query by searching
, and since the column is also used in SELECT queries, I think its better to use
'%Column1 =%'
criteria as

However, system coding standard is not strictly applied, therefore in our database there exists multiple type of codings:
column1 =
column1 {tab} =
column1 +=
column1 {CR}{LF} =
and etc. Definitely i can't use
as predicate, but how can I find all (at least most) of them meet my initial predicate?

Assume I accept maximum 10 chars between
is accepted.

Answer Source

I was in the same situation in the past. What my work around was, I removed every space, new line and tab from sql_modules definition column itself then compared with my searched string.

Select SP_Def from
 (SELECT REPLACE(REPLACE(REPLACE(sm.definition, ' ',''), CHAR(13) +CHAR(10),''),CHAR(9),'') as SP_Def
  FROM sys.sql_modules AS sm ) a
where SP_Def like '%column1=%'