文件夹中每个文件中的 Powershell 更新字符串问题 [英] Issue with Powershell update string in each file within a folder

问题描述

我在一个文件夹中存储了一组 SQL 文件.这些文件包含格式为 dbo_xxxxxx 的模式名称，其中 xxxxxx 是年份 &月份dbo_202001、dbo_202002 等.我希望powershell 脚本在这些SQL 文件中的每一个中将xxxxx 编号替换为一个新编号.我正在使用下面的脚本来实现这一点.然而，问题是它似乎部分匹配旧字符串(而不是匹配完整字符串)并将新字符串放置到位，例如而不是将 [dbo_202001] 替换为 [dbo_201902]，而是在它找到 d、b、o 的任何地方 等，它用 [dbo_201902] 替换它.无论如何要解决这个问题?

$sourceDir = "C:\SQL_Scripts";$SQLScripts = Get-ChildItem $sourceDir *.sql -recforeach ($SQLScripts 中的 $file){(Get-Content $file.PSPath) |Foreach-Object { $_ -replace "[dbo_202001]", "[dbo_201902]";} |设置内容 $file.PSPath -NoNewline}

解决方案

vonPryz 和 marsze 提供了关键的指针:自从 <代码>-replace 运算符 对正则表达式 进行操作(正则表达式)，你必须\-转义特殊字符，例如[和] 以便将它们逐字(作为文字):
$_ -replace '\[dbo_202001\]', '[dbo_201902]'

虽然使用 -replace 操作符通常更可取，[string] 类型的 .Replace() 方法直接提供 逐字(文字)字符串替换，因此也比 -replace 更快.​​
通常，这无关紧要，但在与您类似的情况下，涉及许多迭代，它可能(请注意，替换区分大小写敏感):
$_.Replace('[dbo_202001]', '[dbo_201902]')

有关何时使用 -replace 与 .Replace() 的指南，请参阅底部.

---

您的代码性能可以大大提高:

$sourceDir = 'C:\SQL_Scripts'foreach ($file in Get-ChildItem -File $sourceDir -Filter *.sql -Recurse){# CAVEAT:这会就地覆盖文件.Set-Content -NoNewLine $file.PSPath -Value `(Get-Content -Raw $file.PSPath).Replace('[dbo_202001]', '[dbo_201902]')}

• 由于您无论如何都要将整个文件读入内存，因此使用 Get-Content 的 -Raw 开关将其读取为单行多行字符串(而不是行数组)，您可以在其上执行单个 .Replace() 操作要快得多.

• 设置内容 的 -NoNewLine 开关是必要的，以防止在写回文件时附加额外的换行符.

• 注意使用 -Value 参数而不是 pipeline 来提供文件内容.由于这里只有一个字符串对象要写入，所以差别不大，但一般来说，要写入的许多对象已经收集在内存中，Set-Content ... -Value$array 比 $array | 快得多设置内容 ....

---

-replace 使用指南 operator 与 .Replace() 方法:H3>

• -replace 是特定于 PowerShell 的 regex (regular-expression) 基于字符串替换 operator,
• 而 .Replace() 是 .NET [string] 类型的方法.(System.String), 执行逐字(文字)字符串替换.

请注意，这两个功能总是替换所有他们找到的匹配项，相反，如果没有找到，则返回原始字符串..>

一般来说，PowerShell 的 -replace 运算符更适合 PowerShell 代码 - 无论是在语法上还是由于其不区分大小写 - 并且提供更多功能，这要归功于基于正则表达式.

.Replace() 方法仅限于逐字替换，在 Windows PowerShell 中仅限于区分大小写，但具有以下优点更快并且不必担心在其参数中转义特殊字符:

• 只使用[string]类型的.Replace()方法:

  • 用于总是逐字字符串替换
  • 区分大小写:
    • PowerShell [Core] v6+:默认情况下区分大小写，可选区分大小写不区分，通过附加论证
    • Windows PowerShell:总是(!)区分大小写
  • 如果在功能上可行，当性能很重要时

• 否则，请使用 PowerShell 的 -replace 运算符(详细介绍 此处):

  • 对于基于正则表达式的替换:

    • 启用复杂的、基于模式的匹配和替换字符串的动态构造

    • 要转义元字符(具有特殊句法含义的字符)以便逐字处理:

      • 在模式(正则表达式)参数中:\-转义它们(例如，\. 或 \[)
      • 在替换参数中:只有 $ 是特殊的，将其转义为 $$.

    • 要转义整个操作数以逐字处理其值(有效执行文字替换):

      • 在模式参数中:调用[regex]::Escape($pattern)

      • 在替换参数中:调用 $replacement.Replace('$', '$$')

  • 以下区分大小写:

    • 大小写-不敏感默认
    • 可选区分大小写，通过它的c前缀变体-creplace>

  • 注意:-replace 是围绕 [regex]::Replace() 方法，不会暴露所有后者的功能，尤其是限制替换次数的能力；请参阅此答案了解如何使用它.

注意-replace可以直接操作数组(集合)作为LHS，在这种情况下替换对每个元素执行，按需字符串化.

感谢 PowerShell 的基本成员枚举功能，.Replace() 也可以操作数组，但前提是所有元素都已经是字符串.此外，与 -replace 不同，如果 LHS 为一，它总是也返回一个数组，如果输入对象碰巧是一个单个字符串，则成员枚举返回单个字符串-element 数组.

顺便说一句:类似的考虑适用于使用 PowerShell 的 -split 运算符与 [string] 类型的 .Split()方法 - 请参阅此答案.

示例:

-replace - 有关语法详细信息，请参阅此答案:

# 不区分大小写的替换.# 模式操作数(正则表达式)恰好是一个逐字字符串.PS>'foo' - 替换 'O', '@'F@@# 区分大小写的替换，使用 -creplacePS>'fOo' - 替换 'O', '@'f@o# 逐字替换的基于正则表达式的替换:# 元字符 '$' 限制匹配到 *end*PS>'foo' - 替换 'o$', '@'佛@# 基于正则表达式的动态替换:# '$&'指正则表达式匹配的内容PS>'foo' -replace 'o$', '>>$&<<'fo＞＞o＜＜# 仅 PowerShell [核心]:# 基于脚本块的动态替换.PS>'A1' -replace '\d', { [int] $_.Value + 1 }A2# 数组操作，元素按需字符串化:PS>1..3 - 替换 '^', '0'010203# 转义正则表达式元字符.逐字处理.PS>'你欠我 20 美元' - 替换 '\$20', '20 美元'你欠我20美元.# 同上，通过变量和 [regex]::Escape()PS>$var = '$20';'你欠我 20 美元' -replace [regex]::Escape($var), '20 美元'你欠我20美元.# 转义替换操作数中的$"，以便始终逐字处理:PS>你欠我 20 美元"-替换20 美元"、20 美元"你欠我20美元# 同上，通过变量和 [regex]::Escape()PS>$var = '$20';'你欠我 20 美元' -replace '20 美元', $var.Replace('$', '$$')你欠我20美元.

.Replace():

# 逐字逐字，区分大小写的替换.PS>'foo'.Replace('o', '@')F@@# 没有效果，因为匹配区分大小写.PS>'foo'.Replace('O', '@')富# PowerShell [Core] v6+ only: 选择不区分大小写:PS>'foo'.Replace('O', '@', 'CurrentCultureIgnoreCase')F@@# 对数组的操作，感谢成员枚举:# 在这种情况下返回一个 2 元素的数组.PS>('foo', 'goo').replace('o', '@')F@@G@@#！！失败，因为并非所有数组元素都是 *strings*:PS>('foo', 42).替换('o', '@')... 方法调用失败，因为 [System.Int32] 不包含名为Replace"的方法....

I have a set of SQL files stored in a folder. These files contain the schema name in the format dbo_xxxxxx where xxxxxx is the year & month e.g. dbo_202001, dbo_202002 etc. I want the powershell script to replace the xxxxx number with a new one in each of these SQL files. I'm using the below script to achieve that. However, the issue is that it seems to partially match on the old string (instead of matching on the full string) and puts the new string in place e.g. instead of replacing [dbo_202001] with [dbo_201902], anywhere it finds d, b, o etc. it replaces it with [dbo_201902]. Anyway to fix this?

$sourceDir = "C:\SQL_Scripts"
$SQLScripts = Get-ChildItem $sourceDir *.sql -rec
foreach ($file in $SQLScripts)
{
    (Get-Content $file.PSPath) |
    Foreach-Object { $_ -replace "[dbo_202001]", "[dbo_201902]" } |
    Set-Content $file.PSPath -NoNewline
}

解决方案

vonPryz and marsze have provided the crucial pointers: since the -replace operator operates on regexes (regular expressions), you must \-escape special characters such as [ and ] in order to treat them verbatim (as literals):
$_ -replace '\[dbo_202001\]', '[dbo_201902]'

While use of the -replace operator is generally preferable, the [string] type's .Replace() method directly offers verbatim (literal) string replacements and is therefore also faster than -replace.
Typically, this won't matter, but in situations similar to yours, where many iterations are involved, it may (note that the replacement is case-sensitive):
$_.Replace('[dbo_202001]', '[dbo_201902]')

See the bottom section for guidance on when to use -replace vs. .Replace().

---

The performance of your code can be greatly improved:

$sourceDir = 'C:\SQL_Scripts'
foreach ($file in Get-ChildItem -File $sourceDir -Filter *.sql -Recurse)
{
  # CAVEAT: This overwrites the files in-place.
  Set-Content -NoNewLine $file.PSPath -Value `
    (Get-Content -Raw $file.PSPath).Replace('[dbo_202001]', '[dbo_201902]')
}

• Since you're reading the whole file into memory anyway, using Get-Content's -Raw switch to read it as a single, multi-line string (rather than an array of lines) on which you can perform a single .Replace() operation is much faster.

• Set-Content's -NoNewLine switch is needed to prevent an additional newline from getting appended on writing back to the file.

• Note the use of the -Value parameter rather than the pipeline to provide the file content. Since there's only a single string object to write here, it makes little difference, but in general, with many objects to write that are already collected in memory, Set-Content ... -Value $array is much faster than $array | Set-Content ....

---

Guidance on use of the -replace operator vs. the .Replace() method:

• -replace is the PowerShell-specific regex (regular-expression)-based string replacement operator,
• whereas .Replace() is a method of the .NET [string] type. (System.String), which performs verbatim (literal) string replacements.

Note that both features invariably replace all matches they find, and, conversely, return the original string if none are found.

Generally, PowerShell's -replace operator is a more natural fit in PowerShell code - both syntactically and due to its case-insensitivity - and offers more functionality, thanks to being regex-based.

The .Replace() method is limited to verbatim replacements and in Windows PowerShell to case-sensitive ones, but has the advantage of being faster and not having to worry about escaping special characters in its arguments:

• Only use the [string] type's .Replace() method:

  • for invariably verbatim string replacements
  • with the following case-sensitivity:
    • PowerShell [Core] v6+: case-sensitive by default, optionally case-insensitive via an additional argument
    • Windows PowerShell: invariably(!) case-sensitive
  • if functionally feasible, when performance matters

• Otherwise, use PowerShell's -replace operator (covered in more detail here):

  • for regex-based replacements:

    • enables sophisticated, pattern-based matching and dynamic construction of replacement strings

    • To escape metacharacters (characters with special syntactic meaning) in order to treat them verbatim:

      • in the pattern (regex) argument: \-escape them (e.g., \. or \[)
      • in the replacement argument: only $ is special, escape it as $$.

    • To escape an entire operand in order to treat its value verbatim (to effectively perform literal replacement):

      • in the pattern argument: call [regex]::Escape($pattern)

      • in the replacement argument: call $replacement.Replace('$', '$$')

  • with the following case-sensitivity:

    • case-insensitive by default
    • optionally case-sensitive via its c-prefixed variant, -creplace

  • Note: -replace is a PowerShell-friendly wrapper around the [regex]::Replace() method that doesn't expose all of the latter's functionality, notably not its ability to limit the number of replacements; see this answer for how to use it.

Note that -replace can directly operation on arrays (collections) of strings as the LHS, in which case the replacement is performed on each element, which is stringified on demand.

Thanks to PowerShell's fundamental member enumeration feature, .Replace() too can operate on arrays, but only if all elements are already strings. Also, unlike -replace, which always also returns an array if the LHS is one, member enumeration returns a single string if the input object happens to be a single-element array.

As an aside: similar considerations apply to the use of PowerShell's -split operator vs. the [string] type's .Split() method - see this answer.

Examples:

-replace - see this answer for syntax details:

# Case-insensitive replacement.
# Pattern operand (regex) happens to be a verbatim string.
PS> 'foo' -replace 'O', '@'
f@@

# Case-sensitive replacement, with -creplace
PS> 'fOo' -creplace 'O', '@'
f@o

# Regex-based replacement with verbatim replacement: 
# metacharacter '$' constrains the matching to the *end*
PS> 'foo' -replace 'o$', '@'
fo@

# Regex-based replacement with dynamic replacement: 
# '$&' refers to what the regex matched
PS> 'foo' -replace 'o$', '>>$&<<'
fo>>o<<

# PowerShell [Core] only:
# Dynamic replacement based on a script block.
PS> 'A1' -replace '\d', { [int] $_.Value + 1 }
A2

# Array operation, with elements stringified on demand:
PS> 1..3 -replace '^', '0'
01
02
03

# Escape a regex metachar. to be treated verbatim.
PS> 'You owe me $20' -replace '\$20', '20 dollars'
You owe me 20 dollars. 

# Ditto, via a variable and [regex]::Escape()
PS> $var = '$20'; 'You owe me $20' -replace [regex]::Escape($var), '20 dollars'
You owe me 20 dollars.

# Escape a '$' in the replacement operand so that it is always treated verbatim:
PS> 'You owe me 20 dollars' -replace '20 dollars', '$$20'
You owe me $20

# Ditto, via a variable and [regex]::Escape()
PS> $var = '$20'; 'You owe me 20 dollars' -replace '20 dollars', $var.Replace('$', '$$')
You owe me $20.

.Replace():

# Verbatim, case-sensitive replacement.
PS> 'foo'.Replace('o', '@')
f@@

# No effect, because matching is case-sensitive.
PS> 'foo'.Replace('O', '@')
foo

# PowerShell [Core] v6+ only: opt-in to case-INsensitivity:
PS> 'foo'.Replace('O', '@', 'CurrentCultureIgnoreCase')
f@@

# Operation on an array, thanks to member enumeration:
# Returns a 2 -element array in this case.
PS> ('foo', 'goo').Replace('o', '@')
f@@
g@@

# !! Fails, because not all array elements are *strings*:
PS> ('foo', 42).Replace('o', '@')
... Method invocation failed because [System.Int32] does not contain a method named 'Replace'. ...

这篇关于文件夹中每个文件中的 Powershell 更新字符串问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！