SQL Server 2000 中不区分大小写的 REPLACE() [英] Case-insensitive REPLACE() in SQL Server 2000

问题描述

我有一个字段，其中包含诸如 'Blah-OVER'、'Blah-OveR' 等字符串，并希望在没有 'over's 的情况下选择它们.这只捕获第一种情况(可以这么说)而不是其他情况:

I have a field that contains strings such as 'Blah-OVER', 'Blah-OveR', etc. and want to select them without the 'over's. This only catches the first case (so to speak) and not the others:

SELECT field as "before", REPLACE(field, 'OVER', '') as "after"

我怎样才能让他们都说废话-"(保留剩下的情况)而不试图用另一个嵌套的 REPLACE 函数覆盖每个情况组合?

How do I just get them all to say 'Blah-' (preserving the case of what's left) without attempting to cover every case combination with another nested REPLACE function?

推荐答案

使用不区分大小写的排序规则:

Use a case insensitive collation:

SELECT field as "before", REPLACE(field COLLATE SQL_Latin1_General_Cp1_CI_AI
, 'OVER', '') as "after"

请参阅 COLLATE 以获取排序规则名称列表，以便您选择适合您的数据.

See COLLATE for list of collation names so you choose the one appropiate for your data.

更新

好的，所以我错过了您的实际请求(更改输入的大小写，而不是不区分大小写).正确的解决方案是......不要更改输入，而是为您的数据使用适当的排序规则.如果数据必须以特定格式显示，请使用客户端中的显示选项，例如.CSS text-transform:uppercase，不在服务器 SELECT 中.

Ok, so I missed your actual request (change case of input, not find case-insensitive). The proper solution is... not to change the input but use an adequate collation for your data. If the data must be displayed in a specific format, use display options in the client, eg. CSS text-transform:uppercase, not in the server SELECT.

没有任何内置的 SQL 函数来就地进行这种转换，但是构建一个 CLR 函数，使用 RegEx.(在 SQL 2005 上，而不是在 SQL 2000 上……哦，我需要更多咖啡).

There isn't any built-in SQL function to do this transformation in-place, but is trivial to build a CLR function that uses RegEx. (On SQL 2005, not on SQL 2000... doh, I need more coffe).

这篇关于SQL Server 2000 中不区分大小写的 REPLACE()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！