通过

能够识别增补字符的字符串操作示例

  • 项目

SQL Server 的这一示例说明能够识别增补字符的字符串处理。 此示例说明五个 Transact-SQL 字符串函数的实现。这些函数提供的字符串操作功能与内置函数的功能相同，但增加了增补字符识别功能，因此既能处理 Unicode 字符串，又能处理增补字符串。 这五个函数分别为 lens()、lefts(), rights(), subs() 和 replace_s()，分别等同于内置函数 LEN(), LEFT(), RIGHT(), SUBSTRING() 和 REPLACE() 字符串函数。

先决条件

若要创建和运行此项目，必须安装下列软件：

  • SQL Server 或 SQL Server Express。 您可以从 SQL Server Express Documentation and Samples （Express 文档和示例）网站免费获取 SQL Server Express。

  • SQL Server 开发人员网站提供的 AdventureWorks 数据库

  • .NET Framework SDK 2.0 或更高版本，或 Microsoft Visual Studio 2005 或更高版本。 您可以免费获取 .NET Framework SDK。

  • 此外，还必须满足以下条件：

  • 您使用的 SQL Server 实例必须已启用 CLR 集成。

  • 若要启用 CLR 集成，请执行以下步骤：

    启用 CLR 集成

    • 执行以下 Transact-SQL 命令：

    sp_configure 'clr enabled', 1

    GO

    RECONFIGURE

    GO

    注意注意

    若要启用 CLR，您必须具有 ALTER SETTINGS 服务器级别权限，sysadmin 和 serveradmin 固定服务器角色的成员隐式拥有该权限。

  • 必须在您使用的 SQL Server 实例上安装 AdventureWorks 数据库。

  • 如果您不是要使用的 SQL Server 实例的管理员，则必须让管理员授予您 CreateAssembly 权限，才能完成安装。

生成示例

按照以下说明创建和运行该示例：

  1. 打开 Visual Studio 或 .NET Framework 命令提示符。

  2. 如有必要，为您的示例创建目录。 对于此示例，我们将使用 C:\MySample。

  3. 由于此示例需要签名的程序集，请通过键入以下命令创建一个非对称密钥：

    sn -k SampleKey.snk

  4. 从命令行提示符执行以下代码之一（具体取决于所选的语言)，对示例代码进行编译。

    • Vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /keyfile:Key.snk /target:library SurrogateStringFunction.vb

    • Csc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.XML.dll /keyfile:Key.snk /target:library SurrogateStringFunction.cs

  5. 将 Transact-SQL 安装代码复制到一个文件中，并在示例目录中将其另存为 Install.sql。

  6. 通过执行以下命令部署程序集和存储过程：

    • sqlcmd -E -I -i install.sql -v root = "C:\MySample\"

  7. 将 Transact-SQL 测试命令脚本复制到一个文件中，并在示例目录中将其另存为 test.sql。

  8. 使用以下命令执行测试脚本：

    • sqlcmd -E -I -i test.sql

  9. 将 Transact-SQL 清除脚本复制到一个文件中，并在示例目录中将其另存为 cleanup.sql。

  10. 使用以下命令执行该脚本：

    • sqlcmd -E -I -i cleanup.sql

示例代码

下面是此示例的代码列表。

C#

using System;
using System.Globalization;
using System.Text;

/// <summary>
/// Include several string functions for T-SQL to manipulate surrogate characters.
/// </summary>
public sealed class SurrogateStringFunction
{
/// <summary>
///
/// </summary>
private SurrogateStringFunction()
{}

/// <summary>
/// LenS is equal to T-SQL string function LEN() which returns the number
/// of characters, rather than the number of bytes, of the given string expression.
/// </summary>
/// <param name="value">The input string.</param>
/// <returns>The number of characters in the string.</returns>
public static long LenS(String value)
{
if (value == null) throw new ArgumentNullException("value");

int[] myIndex;
// Remove trailing spaces for situations when the Transact-SQL variable or table column
// uses a fixed length datatype such as nchar(50).
// If the trailing spaces are not excluded, this function will return 50 which is not
// correct or expected.
myIndex = StringInfo.ParseCombiningCharacters(value.TrimEnd());
return (null != myIndex) ? myIndex.Length : 0;
}

/// <summary>
/// SubS only support character expression of T-SQL funciton SUBSTRING()
/// which returns part of a string.
/// </summary>
/// <param name="value">The input string.</param>
/// <param name="start">The position of the first character that will be returned.</param>
/// <param name="length">The number of characters to return.</param>
/// <returns>The string found at the starting position for the specified
/// number characters.</returns>

public static String SubS(String value, int start, int length)
{
if (value == null) throw new ArgumentNullException("value");
if (length < 0)
                throw new ArgumentException("Invalid length parameter passed to the substring function.");

// In Transact-SQL, the substring method initializes to 1. So, start should be initialized to at least 1.
// Length also has to be at least 1 or the Transact-SQL result would be an empty string.
            if ((start + length) <= 1)
                return (String.Empty);

// The 2 if statements below guarentee that the result will match the substring function in 
// Transact-SQL which will initialize start to 1 by subtracting from the length.
            if (start <= 0 && length > 0)
                length--;

            if ((start <= 0))
            {
                length = length + start;
                start = 1;
            }

            int[] myIndex;
myIndex = StringInfo.ParseCombiningCharacters(value);
int NumOfIndexes = (null != myIndex) ? myIndex.Length : 0;

            start--;
            if ((0 <= start) && (start < NumOfIndexes))
{
int lastIndex = start + length;

// if we are past the last char, then we get the string
// up to the last char 
if (lastIndex > (NumOfIndexes - 1))
{
return value.Substring(myIndex[start]);
}
else
{
return value.Substring(myIndex[start], myIndex[lastIndex] - myIndex[start]);
}
}
else
{
return String.Empty;
}
}

// 
//    
/// <summary>
/// LeftS is equal to T-SQL string function LEFT() which returns the left
/// part of a character string with the specified number of characters.
/// </summary>
/// <param name="value">The input string.</param>
/// <param name="start">The position of the first character that will be returned.</param>
/// <param name="length">The number of characters to return.</param>
/// <returns>The string found at the starting position for n-length.</returns>

public static String LeftS(String value, int length )
{
if (length < 0)
throw new ArgumentException("length must be a positive integer");

return SubS(value, 1, length);
}

// RightS is equal to T-SQL string function RIGHT() which returns the right
//    part of a character string with the specified number of characters.

public static String RightS(String value, int length)
{
if (length < 0)
throw new NotSupportedException("length must be a positive integer");
if (value == null) throw new ArgumentNullException("value");

int[] myIndex;

myIndex = StringInfo.ParseCombiningCharacters(value);
int numOfIndexes = (null != myIndex) ? myIndex.Length : 0;

if (numOfIndexes <= length)
return value;

if (length == 0) return String.Empty;

int virtualStartIndex = numOfIndexes - length;
int physicalStartIndex = myIndex[virtualStartIndex];
return value.Substring(physicalStartIndex);

}

//
// ReplaceS is equal to T-SQL string function REPLACE() which replaces all
// occurrences of the second given string expression in the first string expression
// with a third expression.
//
public static String ReplaceS(String value, String replaceValue, String newValue)
{
StringBuilder result = new StringBuilder(value.Length);
int[] myIndex;
int i = 0;
String upperValue = value.ToUpper(CultureInfo.CurrentUICulture);
String upperReplaceValue = replaceValue.ToUpper(CultureInfo.CurrentUICulture);

myIndex = StringInfo.ParseCombiningCharacters(upperValue);
while (i < value.Length)
{
int possibleMatch = upperValue.IndexOf(upperReplaceValue, i);
if (possibleMatch < 0)
{
result.Append(value.Substring(i));
break;
}
else
{
//Ensure we're not matching a partial surrogate
int surrogateIndex = Array.IndexOf<int>(myIndex, possibleMatch);
if (surrogateIndex < 0)
{
//We've matched in the middle of a surrogate, skip this match
//as it is not valid.
int nextStart = possibleMatch + 1;
result.Append(value.Substring(i, nextStart-i));
i = nextStart;
}
else
{
//This is a valid match.  Make the substitution.
result.Append(value.Substring(i, possibleMatch - i));
result.Append(newValue);
i = possibleMatch + replaceValue.Length;
}
}
}
return result.ToString();
}
}

Visual Basic

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections
Imports System.Data
Imports System.Diagnostics
Imports System.Globalization
Imports System.Text
''' <summary>
''' Include several string functions for T-SQL to manipulate surrogate characters.
''' </summary>

Public NotInheritable Class SurrogateStringFunction
    ''' <summary>
    ''' Empty default constructor
    ''' </summary>
    Private Sub New()
    End Sub

    ''' <summary>
    ''' LenS is equal to T-SQL string function LEN() which returns the number
    ''' of characters, rather than the number of bytes, of the given string expression.
    ''' </summary>
    ''' <param name="value">The input string.</param>
    ''' <returns>The number of characters in the string.</returns>
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function LenS(ByVal value As String) As Long
        If value Is Nothing Then
            Throw New ArgumentNullException("value")
        End If

        Dim myIndex() As Integer
        ' Remove trailing spaces for situations when the Transact-SQL variable or table column
        ' uses a fixed length datatype such as nchar(50).
        ' If the trailing spaces are not excluded, this function will return 50 which is not
        ' correct or expected.
        myIndex = StringInfo.ParseCombiningCharacters(value.TrimEnd())

        If (myIndex IsNot Nothing) Then
            Return myIndex.Length
        Else
            Return 0
        End If
    End Function

    ''' <summary>
    ''' SubS only support character expression of T-SQL funciton SUBSTRING()
    ''' which returns part of a string.
    ''' </summary>
    ''' <param name="value">The input string.</param>
    ''' <param name="start">The position of the first character that will be returned.</param>
    ''' <param name="length">The number of characters to return.</param>
    ''' <returns>The string found at the starting position for the specified
    ''' number characters.</returns>
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function SubS(ByVal value As String, ByVal start As Integer, ByVal length As Integer) As String
        If value Is Nothing Then
            Throw New ArgumentNullException("value")
        End If

        If length < 0 Then
            Throw New ArgumentException("Invalid length parameter passed to the substring function.")
        End If

        ' In Transact-SQL, the substring method initializes to 1. So, start should be initialized to at least 1.
        ' Length also has to be at least 1 or the Transact-SQL result would be an empty string.
        If start + length <= 1 Then
            Return String.Empty
        End If

        ' The 2 if statements below guarentee that the result will match the substring function in 
        ' Transact-SQL which will initialize start to 1 by subtracting from the length.
        If start <= 0 AndAlso length > 0 Then
            length -= 1
        End If

        If start <= 0 Then
            length = length + start
            start = 1
        End If

        Dim myIndex() As Integer
        myIndex = StringInfo.ParseCombiningCharacters(value)

        Dim NumOfIndexes As Integer
        If (myIndex IsNot Nothing) Then
            NumOfIndexes = myIndex.Length
        Else
            NumOfIndexes = 0
        End If

        start -= 1
        If 0 <= start AndAlso start < NumOfIndexes Then
            Dim lastIndex As Integer = start + length

            ' if we are past the last char, then we get the string
            ' up to the last char 
            If lastIndex > NumOfIndexes - 1 Then
                Return value.Substring(myIndex(start))
            Else
                Return value.Substring(myIndex(start), myIndex(lastIndex) - myIndex(start))
            End If
        Else
            Return String.Empty
        End If
    End Function

    ''' <summary>
    ''' LeftS is equal to T-SQL string function LEFT() which returns the left
    ''' part of a character string with the specified number of characters.
    ''' </summary>
    ''' <param name="value">The input string.</param>
    ''' <param name="length">The number of characters to return.</param>
    ''' <returns>The string found at the starting position for n-length.</returns>
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function LeftS(ByVal value As String, ByVal length As Integer) As String
        If length < 0 Then
            Throw New ArgumentException("Length must be a positive integer")
        End If

        Return SubS(value, 1, length)
    End Function

    ' RightS is equal to T-SQL string function RIGHT() which returns the right
    '    part of a character string with the specified number of characters.
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function RightS(ByVal value As String, ByVal length As Integer) As String
        If value Is Nothing Then
            Throw New ArgumentNullException("value")
        End If

        If length < 0 Then
            Throw New NotSupportedException("Length must be a positive integer")
        End If

        Dim myIndex() As Integer

        myIndex = StringInfo.ParseCombiningCharacters(value)
        Dim NumOfIndexes As Integer
        If (myIndex IsNot Nothing) Then
            NumOfIndexes = myIndex.Length
        Else
            NumOfIndexes = 0
        End If


        If NumOfIndexes <= length Then
            Return value
        End If

        If length = 0 Then
            Return String.Empty
        End If

        Dim virtualStartIndex As Integer = NumOfIndexes - length
        Dim physicalStartIndex As Integer = myIndex(virtualStartIndex)

        Return value.Substring(physicalStartIndex)
    End Function

    ''' <summary>
    ''' ReplaceS is equal to T-SQL string function REPLACE() which replaces all
    ''' occurrences of the second given string expression in the first string expression
    ''' with a third expression.
    ''' </summary>
    ''' <param name="value"></param>
    ''' <param name="replaceValue"></param>
    ''' <param name="newValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function ReplaceS(ByVal value As String, ByVal replaceValue As String, ByVal newValue As String) As String
        Dim result As New StringBuilder(value.Length)
        Dim myIndex() As Integer
        Dim i As Integer = 0
        Dim upperValue As String = value.ToUpper(CultureInfo.CurrentUICulture)
        Dim upperReplaceValue As String = replaceValue.ToUpper(CultureInfo.CurrentUICulture)

        myIndex = StringInfo.ParseCombiningCharacters(upperValue)

        While i < value.Length
            Dim possibleMatch As Integer = upperValue.IndexOf(upperReplaceValue, i)
            If possibleMatch < 0 Then
                result.Append(value.Substring(i))
                Exit While
            Else
                'Ensure we're not matching a partial surrogate
                Dim surrogateIndex As Integer = Array.IndexOf(Of Integer)(myIndex, possibleMatch)
                If surrogateIndex < 0 Then
                    'We've matched in the middle of a surrogate, skip this match
                    'as it is not valid.
                    Dim nextStart As Integer = possibleMatch + 1
                    result.Append(value.Substring(i, nextStart - i))
                    i = nextStart
                Else
                    'This is a valid match.  Make the substitution.
                    result.Append(value.Substring(i, possibleMatch - i))
                    result.Append(newValue)
                    i = possibleMatch + replaceValue.Length
                End If
            End If
        End While

        Return result.ToString()
    End Function
End Class

这是 Transact-SQL 安装脚本 (Install.sql)，该脚本在数据库中部署程序集并创建能够识别增补字符的函数。

Use [AdventureWorks]
Go

IF OBJECT_ID('[dbo].[len_s]') IS NOT NULL
  DROP FUNCTION [dbo].[len_s];

IF OBJECT_ID('[dbo].[sub_s]') IS NOT NULL
  DROP FUNCTION [dbo].[sub_s];

IF OBJECT_ID('[dbo].[left_s]') IS NOT NULL
  DROP FUNCTION [dbo].[left_s];

IF OBJECT_ID('[dbo].[right_s]') IS NOT NULL
  DROP FUNCTION [dbo].[right_s];

IF OBJECT_ID('[dbo].[replace_s]') IS NOT NULL
  DROP FUNCTION [dbo].[replace_s];
GO

IF EXISTS (SELECT [name] FROM sys.assemblies
             WHERE [name] = 'SurrogateStringFunction')
  DROP ASSEMBLY SurrogateStringFunction;
GO

USE master
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'ExternalSample_Login')
DROP LOGIN ExternalSample_Login;
GO

IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'ExternalSample_Key')
DROP ASYMMETRIC KEY ExternalSample_Key;
GO

--Before we register the assembly to SQL Server, we must arrange for the appropriate permissions.
--Assemblies with unsafe or external_access permissions can only be registered and operate correctly
--if either the database trustworthy bit is set or if the assembly is signed with a key,
--that key is registered with SQL Server, a server principal is created from that key,
--and that principal is granted the external access or unsafe assembly permission.  We choose
--the latter approach as it is more granular, and therefore safer.  You should never
--register an assembly with SQL Server (especially with external_access or unsafe permissions) without
--thoroughly reviewing the source code of the assembly to make sure that its actions 
--do not pose an operational or security risk for your site.

DECLARE @SamplesPath nvarchar(1024);

-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
set @SamplesPath = N'C:\MySample\'

EXEC('CREATE ASYMMETRIC KEY ExternalSample_Key FROM EXECUTABLE FILE = ''' + @SamplesPath 
    + 'SurrogateStringFunction.dll'';');
CREATE LOGIN ExternalSample_Login FROM ASYMMETRIC KEY ExternalSample_Key
GRANT EXTERNAL ACCESS ASSEMBLY TO ExternalSample_Login;
GO

USE AdventureWorks;
GO

--
-- Create assembly to register class methods for create functions
-- 
DECLARE @SamplesPath nvarchar(1024);
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.

set @SamplesPath = N'C:\MySample\'

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[SurrogateStringFunction].[LenS];
GO

CREATE FUNCTION [dbo].[sub_s](@str nvarchar(4000), @pos int, @cont int)
RETURNS nvarchar(4000)
AS EXTERNAL NAME [SurrogateStringFunction].[SurrogateStringFunction].[SubS];
GO

CREATE FUNCTION [dbo].[left_s](@str nvarchar(4000), @cont int)
RETURNS nvarchar(4000)
AS EXTERNAL NAME [SurrogateStringFunction].[SurrogateStringFunction].[LeftS];
GO

CREATE FUNCTION [dbo].[right_s](@str nvarchar(4000), @cont int)
RETURNS nvarchar(4000)
AS EXTERNAL NAME [SurrogateStringFunction].[SurrogateStringFunction].[RightS];
GO

CREATE FUNCTION [dbo].[replace_s](@str nvarchar(4000), @str1 nvarchar(4000), @str2 nvarchar(4000))
RETURNS nvarchar(4000)
AS EXTERNAL NAME [SurrogateStringFunction].[SurrogateStringFunction].[ReplaceS];
GO

这是 test.sql，该脚本通过执行函数测试该示例。

Use [AdventureWorks]
Go

-- left_s  VS  Left
print ('***** left_s VS Left *****');
select [dbo].[left_s](N'౷౸123',2);
print(N'Should Return ౷౸');
go
select Left(N'౷౸123',2);
print(N'Will Return ౷');
go

-- right_s VS Right
print ('***** right_s VS Right *****')
select [dbo].[right_s](N'౷౸123',5);
print(N'Should Return ౷౸123');
go
select Right(N'౷౸123',5);
print(N'Will Return ౸123');
go

-- len_s  VS Len
print('***** len_s VS Len *****');
select [dbo].[len_s](N'ƾǀǃ12');
print(N'Should Return 5');
go
select Len(N'ƾǀǃ12');
print(N'Will Return 8');
go

-- sub_s VS Substring
print('***** sub_s VS Subscription *****');
select [dbo].[sub_s] (N'♢♣♤♥♦♧♨♩♪♫♬♭♮♯♰♱♲♳',3,5);
print(N'Should Return ♤♥♦♧♨');
go
select substring(N'♢♣♤♥♦♧♨♩♪♫♬♭♮♯♰♱♲♳',3,5);
print(N'Will Return ♣♤');
go

-- replace_s VS Replace
print('***** replace_s VS Replace *****');
select [dbo].[replace_s](N'ᥕᥖᥗᥙᥚᥛᥕᥖᥗᥙᥚᥛ',N'ᥗᥙᥚ',N'ᦼ');
print(N'Should Return ᥖᦼᥛᥕᥖᦼᥛ');
go
select replace(N'ᥕᥖᥗᥙᥚᥛᥕᥖᥗᥙᥚᥛ',N'ᥗᥙᥚ',N'ᦼ');
print(N'Will Return ᦼ');
go

下面的 Transact-SQL 从数据库中删除程序集和函数。

-- Drop assemblies and functions if they exist.
USE [AdventureWorks]
GO

IF OBJECT_ID('[dbo].[len_s]') IS NOT NULL
  DROP FUNCTION [dbo].[len_s];

IF OBJECT_ID('[dbo].[sub_s]') IS NOT NULL
  DROP FUNCTION [dbo].[sub_s];

IF OBJECT_ID('[dbo].[left_s]') IS NOT NULL
  DROP FUNCTION [dbo].[left_s];

IF OBJECT_ID('[dbo].[right_s]') IS NOT NULL
  DROP FUNCTION [dbo].[right_s];

IF OBJECT_ID('[dbo].[replace_s]') IS NOT NULL
  DROP FUNCTION [dbo].[replace_s];
GO

IF EXISTS (SELECT [name] FROM sys.assemblies
             WHERE [name] = 'SurrogateStringFunction')
  DROP ASSEMBLY SurrogateStringFunction;
GO

USE master
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'ExternalSample_Login')
DROP LOGIN ExternalSample_Login;
GO

IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'ExternalSample_Key')
DROP ASYMMETRIC KEY ExternalSample_Key;
GO

USE [AdventureWorks]
GO

请参阅

概念

公共语言运行时 (CLR) 集成的使用方案和示例