如何在SQL 2000/2005中执行区分大小写的搜索和替换？

In order to perform a case-sensitive search/replace on a table in a SQL Server 2000/2005 database, you must use the correct collation.

为了在SQL Server 2000/2005数据库中的表上执行区分大小写的搜索/替换,您必须使用正确的排序规则。

How do you determine whether the default collation for a database is case-sensitive, and if it isn't, how to perform a case-sensitive search/replace?

如何确定数据库的默认排序规则是否区分大小写,如果不是,则如何执行区分大小写的搜索/替换?

7 个解决方案

#1

SELECT testColumn FROM testTable  
    WHERE testColumn COLLATE Latin1_General_CS_AS = 'example' 

SELECT testColumn FROM testTable
    WHERE testColumn COLLATE Latin1_General_CS_AS = 'EXAMPLE' 

SELECT testColumn FROM testTable 
    WHERE testColumn COLLATE Latin1_General_CS_AS = 'eXaMpLe' 

Don't assume the default collation will be case sensitive, just specify a case sensitive one every time (using the correct one for your language of course)

不要假设默认排序规则区分大小写,每次只指定一个区分大小写(当然使用正确的语言)

#2

Determine whether the default collation is case-sensitive like this:

确定默认排序规则是否区分大小写,如下所示:

select charindex('RESULT', 'If the result is 0 you are in a case-sensitive collation mode')

select charindex('RESULT','如果结果为0,则表示区分大小写的排序模式')

A result of 0 indicates you are in a case-sensitive collation mode, 8 indicates it is case-insensitive.

结果为0表示您处于区分大小写的排序规则模式,8表示它不区分大小写。

If the collation is case-insensitive, you need to explicitly declare the collation mode you want to use when performing a search/replace.

如果排序规则不区分大小写,则需要在执行搜索/替换时显式声明要使用的排序规则模式。

Here's how to construct an UPDATE statement to perform a case-sensitive search/replace by specifying the collation mode to use:

以下是如何通过指定要使用的排序规则模式来构造UPDATE语句以执行区分大小写的搜索/替换:

update ContentTable
set ContentValue = replace(ContentValue COLLATE Latin1_General_BIN, 'THECONTENT', 'TheContent')
from StringResource
where charindex('THECONTENT', ContentValue COLLATE Latin1_General_BIN) > 0

This will match and replace 'THECONTENT', but not 'TheContent' or 'thecontent'.

这将匹配并替换'THECONTENT',但不是'TheContent'或'thecontent'。

#3

If you have different cases of the same word in the same field, and only want to replace specific cases, then you can use collation in your REPLACE function:

如果在同一个字段中有相同单词的不同情况,并且只想替换特定情况,则可以在REPLACE函数中使用排序规则:

UPDATE tableName
SET fieldName = 
    REPLACE(
        REPLACE(
            fieldName COLLATE Latin1_General_CS_AS,
            'camelCase' COLLATE Latin1_General_CS_AS,
            'changedWord'
        ),
        'CamelCase' COLLATE Latin1_General_CS_AS,
        'ChangedWord'
    )

This will result in:

这将导致:

This is camelCase 1 and this is CamelCase 2

becoming:

This is changedWord 1 and this is ChangedWord 2

#4

Can be done in multiple statements. This will not work if you have long strings that contain both capitalized an lowercase words you intend to replace. You might also need to use different collation this is accent and case sensitive.

可以在多个语句中完成。如果您的长字符串包含您要替换的大小写单词,那么这将不起作用。您可能还需要使用不同的排序规则,这是重音和区分大小写的。

UPDATE T SET [String] = ReplacedString
FROM [dbo].[TranslationText] T, 
    (SELECT [LanguageCode]
      ,[StringNo]
      ,REPLACE([String], 'Favourite','Favorite') ReplacedString
    FROM [dbo].[TranslationText]
    WHERE 
    [String] COLLATE Latin1_General_CS_AS like '%Favourite%'
    AND [LanguageCode] = 'en-us') US_STRINGS
WHERE 
T.[LanguageCode] = US_STRINGS.[LanguageCode] 
AND T.[StringNo] = US_STRINGS.[StringNo]

UPDATE T SET [String] = ReplacedString
FROM [dbo].[TranslationText] T, 
    (SELECT [LanguageCode]
      ,[StringNo]
      , REPLACE([String], 'favourite','favorite') ReplacedString 
    FROM [dbo].[TranslationText]
    WHERE 
    [String] COLLATE Latin1_General_CS_AS like '%favourite%'
    AND [LanguageCode] = 'en-us') US_STRINGS
WHERE 
T.[LanguageCode] = US_STRINGS.[LanguageCode] 
AND T.[StringNo] = US_STRINGS.[StringNo]

#5

First of all check this: http://technet.microsoft.com/en-us/library/ms180175(SQL.90).aspx

首先检查一下:http://technet.microsoft.com/en-us/library/ms180175(SQL.90).aspx

You will see that CI specifies case-insensitive and CS specifies case-sensitive.

您将看到CI指定不区分大小写,CS指定区分大小写。

#6

Also, this might be usefull. select * from fn_helpcollations() - this gets all the collations your server supports. select * from sys.databases - here there is a column that specifies what collation has every database on your server.

此外,这可能是有用的。 select * from fn_helpcollat​​ions() - 这将获得服务器支持的所有排序规则。 select * from sys.databases - 这里有一列指定服务器上每个数据库的排序规则。

#7

You can either specify the collation every time you query the table or you can apply the collation to the column(s) permanently by altering the table.

您可以在每次查询表时指定排序规则,也可以通过更改表永久地将排序规则应用于列。

If you do choose to do the query method its beneficial to include the case insensitive search arguments as well. You will see that SQL will choose a more efficient exec plan if you include them. For example:

如果您确实选择执行查询方法,那么包含不区分大小写的搜索参数也是有益的。如果包含SQL,您将看到SQL将选择更高效的exec计划。例如:

SELECT testColumn FROM testTable 
    WHERE testColumn COLLATE Latin1_General_CS_AS = 'eXaMpLe' 
    and testColumn = 'eXaMpLe'