如何从 T-SQL 中的字符串中删除扩展的 ASCII 字符?

我需要从 T-SQL 的 SELECT 语句中过滤掉(删除)扩展的 ASCII 字符.

我正在使用存储过程来执行此操作.

预期输入:

ËËËËeeeeËËËË

预期输出:

eeee

我发现的所有内容都适用于 MySQL.>

我正在使用:

Microsoft SQL Server Management Studio 11.0.2100.60微软 .NET 框架 4.0.30319.17929

解决方案

好的，试试这个.他们似乎有同样的问题.无论如何，您需要根据您的要求对其进行修改.

CREATE FUNCTION RemoveNonASCII(@nstring nvarchar(255))返回 varchar(255)作为开始声明@Result varchar(255)SET @Result = ''声明@nchar nvarchar(1)声明 @position int设置@位置 = 1而@position <= LEN(@nstring)开始SET @nchar = SUBSTRING(@nstring, @position, 1)--Unicode &ASCII 从 1 到 255 是相同的.--只有Unicode超过255--0 到 31 是不可打印的字符如果 UNICODE(@nchar) 介于 32 和 255 之间SET @Result = @Result + @ncharSET @position = @position + 1结尾返回@Result结尾去

在 SqlServerCentral

中查看

I need to filter out (remove) extended ASCII characters from a SELECT statement in T-SQL.

I'm using a stored procedure to do so.

Expected input:

ËËËËeeeeËËËË

Expected output:

eeee

All that I've found is for MySQL.

I'm using :

Microsoft SQL Server Management Studio  11.0.2100.60
Microsoft .NET Framework    4.0.30319.17929

解决方案

OK, give this a try. It seems the same issue they have. Anyway you need to modify it based on your requirements.

CREATE FUNCTION RemoveNonASCII 
(
    @nstring nvarchar(255)
)
RETURNS varchar(255)
AS
BEGIN

    DECLARE @Result varchar(255)
    SET @Result = ''

    DECLARE @nchar nvarchar(1)
    DECLARE @position int

    SET @position = 1
    WHILE @position <= LEN(@nstring)
    BEGIN
        SET @nchar = SUBSTRING(@nstring, @position, 1)
        --Unicode & ASCII are the same from 1 to 255.
        --Only Unicode goes beyond 255
        --0 to 31 are non-printable characters
        IF UNICODE(@nchar) between 32 and 255
            SET @Result = @Result + @nchar
        SET @position = @position + 1
    END

    RETURN @Result

END
GO

Check it out at SqlServerCentral