Finds items in the database with a special character using Sitecore PowerShell Extensions.

FindItemsWithSpecialCharacter.ps1

Import-Function -Name Invoke-SqlCommand
    
$connection = [Sitecore.Configuration.Settings]::GetConnectionString("master")

$text = '%' + 'Ã' + '%'
$query = @"
select [ItemID], [FieldID], LEN([Value]) AS [FieldLength] from [SharedFields] WITH (NOLOCK) where [Value] like @0
"@
$shared = Invoke-SqlCommand -Connection $connection -Query $query -Parameters @{"@0"=$text}
$query = @"
select [ItemID], [FieldID], LEN([Value]) AS [FieldLength], [Language] from [UnversionedFields] WITH (NOLOCK) where [Value] like @0
"@
$unversioned = Invoke-SqlCommand -Connection $connection -Query $query -Parameters @{"@0"=$text}
$query = @"
select [ItemID], [FieldID], LEN([Value]) AS [FieldLength], [Language], [Version] from [VersionedFields] WITH (NOLOCK) where [Value] like @0
"@
$versioned = Invoke-SqlCommand -Connection $connection -Query $query -Parameters @{"@0"=$text}

$foundItems = [System.Collections.ArrayList]@()
if($shared) {
    $foundItems.AddRange(@($shared))
}
if($unversioned) {
    $foundItems.AddRange(@($unversioned))
}
if($versioned) {
    $foundItems.AddRange(@($versioned))
}

$itemLookup = @{}

foreach($foundItem in $foundItems) {
    if($itemLookup.ContainsKey($foundItem.ItemId)) { continue }
    $itemLookup[$foundItem.ItemId] = Get-Item -Path "master:" -ID $foundItem.ItemId
}

$fieldLookup = @{}
foreach($foundItem in $foundItems) {
    if($fieldLookup.ContainsKey($foundItem.FieldId)) { continue }
    $fieldLookup[$foundItem.FieldId] = Get-Item -Path "master:" -ID $foundItem.FieldId | Select-Object -Expand Name
}

$props = @{
    Title = "Custom Report"
    InfoTitle = "Details about this report"
    InfoDescription = "This report provides some interesting details about the items."
    PageSize = 25
    Property = @(
        @{Label="Field Name"; Expression={$fieldLookup[$_.FieldId]} },
        @{Label="Path"; Expression={$itemLookup[$_.ItemId].ItemPath} },
        "FieldLength"
    )
}

$foundItems | Show-ListView @props
Close-Window

<#
foreach($foundItem in $foundItems) {
    $item = $itemLookup[$foundItem.ItemId]
    if($item.ItemPath -like "/sitecore/media library/*") {
        $fieldName = $fieldLookup[$foundItem.FieldId]
        $fieldValue = $item.$fieldName
        $item.$fieldName = $fieldValue.Replace($text.Replace("%",""), "")
    }
}
#>