a4194304 a4194304 - 1 month ago 10
SQL Question

SQL Use LIKE predicate to match empty string

I want to find which SP a column named

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

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

Assume I accept maximum 10 chars between
column1
and
=
is accepted.

Answer

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=%'