Nov 4, 2015

SQL Server function Compare two string C# equivalents

Compares substrings of two specified string and returns an integer that indicates their relative position in the sort order in SQL server. It is same as CompareOrdinal and CompareTo in C# or VB.Net:


CREATE FUNCTION [dbo].[Compare] (
      @StrA NVARCHAR(MAX)
      ,@StrB NVARCHAR(MAX)
      ,@IndexA INT = 1
      ,@IndexB INT = 1
      ,@Length INT = NULL
      ,@ignoreCase BIT = 0
      )
RETURNS INT
AS
BEGIN
      DECLARE @SubStrA NVARCHAR(MAX)
      DECLARE @SubStrB NVARCHAR(MAX)
      DECLARE @UniCodeA AS INT
      DECLARE @UniCodeB AS INT

      IF @Length IS NULL
            SET @Length = LEN(@StrA) + LEN(@StrB)

      IF @ignoreCase = 1
      BEGIN
            SET @SubStrA = LOWER(SUBSTRING(@StrA, @IndexA, @Length))
            SET @SubStrB = LOWER(SUBSTRING(@StrB, @IndexB, @Length))
      END
      ELSE
      BEGIN
            SET @SubStrA = SUBSTRING(@StrA, @IndexA, @Length)
            SET @SubStrB = SUBSTRING(@StrB, @IndexB, @Length)
      END

      SET @UniCodeA = UNICODE(@SubStrA)
      SET @UniCodeB = UNICODE(@SubStrB)

      WHILE @UniCodeA = @UniCodeB
      BEGIN
            SET @SubStrA = STUFF(@SubStrA, 1, 1, '')
            SET @SubStrB = STUFF(@SubStrB, 1, 1, '')
            SET @UniCodeA = UNICODE(@SubStrA)
            SET @UniCodeB = UNICODE(@SubStrB)
      END

      RETURN ISNULL(@UniCodeA, 0) - ISNULL(@UniCodeB, 0)
END


Example:
SELECT dbo.Compare('a', 'A', DEFAULT, DEFAULT, DEFAULT, DEFAULT)


Output: 32

No comments:

Post a Comment