删除 ASCII 扩展字符 128 及以上 (SQL) [英] Remove ASCII Extended Characters 128 onwards (SQL)

问题描述

是否有一种简单的方法可以删除 varchar(max) 中的扩展 ASCII 字符.我想从 128 开始删除所有 ASCII 字符.例如 -ù,ç,Ä

Is there a simple way to remove extended ASCII characters in a varchar(max). I want to remove all ASCII characters from 128 onwards. eg - ù,ç,Ä

我已经尝试过这个解决方案但它不起作用，我认为是因为它们仍然是有效的 ASCII 字符?

I have tried this solution and its not working, I think its because they are still valid ASCII characters?

我该怎么做从 T-SQL 中的字符串中删除扩展的 ASCII 字符?

谢谢

推荐答案

链接的解决方案正在使用循环，如果可能的话，您应该避免使用循环.

The linked solution is using a loop which is - if possible - something you should avoid.

我的解决方案是完全可内联的，很容易由此创建 UDF(或者甚至更好:内联 TVF).

My solution is completely inlineable, it's easy to create an UDF (or maybe even better: an inline TVF) from this.

想法:创建一组运行数字(这里受 sys.objects 中对象数量的限制，但有大量示例如何即时创建数字计数).在第二个 CTE 中，字符串被拆分为单个字符.最终选择返回清理后的字符串.

The idea: Create a set of running numbers (here it's limited with the count of objects in sys.objects, but there are tons of example how to create a numbers tally on the fly). In the second CTE the strings are splitted to single characters. The final select comes back with the cleaned string.

DECLARE @tbl TABLE(ID INT IDENTITY, EvilString NVARCHAR(100));
INSERT INTO @tbl(EvilString) VALUES('ËËËËeeeeËËËË'),('ËaËËbËeeeeËËËcË');

WITH RunningNumbers AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nmbr
    FROM sys.objects
)
,SingleChars AS
(
    SELECT tbl.ID,rn.Nmbr,SUBSTRING(tbl.EvilString,rn.Nmbr,1) AS Chr
    FROM @tbl AS tbl
    CROSS APPLY (SELECT TOP(LEN(tbl.EvilString)) Nmbr FROM RunningNumbers) AS rn 
)
SELECT ID,EvilString
      ,(
        SELECT '' + Chr 
        FROM SingleChars AS sc
        WHERE sc.ID=tbl.ID AND ASCII(Chr)<128
        ORDER BY sc.Nmbr
        FOR XML PATH('')
      ) AS GoodString
FROM @tbl As tbl

结果

1   ËËËËeeeeËËËË    eeee
2   ËaËËbËeeeeËËËcË abeeeec

这是我的另一个答案，该方法用于替换所有特殊字符使用 secure 字符来获得 纯拉丁语

Here is another answer from me where this approach is used to replace all special characters with secure characters to get plain latin

这篇关于删除 ASCII 扩展字符 128 及以上 (SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！