0

I'm using UltraEdit's (on a Mac) Find and Replace In Files with regular expressions to clean up SQL Server code blocks in multiple files. And I am noticing that Find in Files outputs expected result, but Replace In Files does not make the replaces as expected.

For example, convert lower case case-when-then blocks to upper case CASE-WHEN-THEN:

Screen shot of Replace in Files window with the search and replace string

FIND IN FILES

case*when*then correctly finds this line:

case when a1c.optimum = 1 and ldl.optimum = 1 and sbp.optimum = 1 and dbp.optimum = 1 then 1 else 0 end optimum,

REPLACE IN FILES

case*when*then to CASE*WHEN*THEN results for the line above in following line with when and then not replaced as expected to upper case words:

CASE*WHEN*THEN 1 else 0 end optimum,

What is wrong on Replace In Files?

Mofi
  • 46,139
  • 17
  • 80
  • 143
Jim Horn
  • 879
  • 6
  • 14

1 Answers1

1

You are obviously using the UltraEdit regular expression engine where * matches any character except newline characters 0 or more times.

You have to use a tagged regular expression to keep parts of found strings unmodified.

Search string: case^(*^)when^(*^)then

Replace string: CASE^1WHEN^2THEN

The same replace using Unix or Perl regular expression engine:

Search string: case(.*)when(.*)then

Replace string: CASE\1WHEN\2THEN

See Perl regular expression using backreferences from IDM Power Tips for an explanation.

By the way: Much better would be with Perl regular expression engine:

Search string: \bcase\b(.*?)\bwhen\b(.*?)\bthen\b

Replace string: CASE\1WHEN\2THEN

\b means word boundary and therefore case, when and then must be entire words and not just 3 strings which each could exist also anywhere within a word.

Mofi
  • 46,139
  • 17
  • 80
  • 143