Version 4.1 of SQL# has just been released!

New functionality available only in Full version

1. Sys_LockResource
   • Returns the name of the specified Lock Resource given the Type, Subtype, DatabaseID, Description, and AssociatedEntityID.
   • Works similarly to the OBJECT_NAME and OBJECT_SCHEMA_NAME built-in functions in that you do not need to be in the database where the item exists in order to get the correct result
   • Currently not all lock resource types and subtypes can be translated. Hopefully over time additional types and subtypes will be able to be translated.
   • For use with Dynamic Management objects that do not return the lock resource name, such as sys.dm_tran_locks
   • Example:

     BEGIN TRAN;
     CREATE TABLE #Test (Col1 INT);
     SELECT tl.[request_session_id], db.[name] AS [DatabaseName],
          SQL#.Sys_LockResource(tl.[resource_type],
                  tl.[resource_subtype], tl.[resource_database_id],
                  tl.[resource_description],
            tl.[resource_associated_entity_id]) AS [LockResource],
          tl.[request_mode], tl.[request_type], tl.[request_status],
          tl.[resource_type], tl.[resource_subtype],
          tl.[resource_description], tl.[request_owner_id]
     FROM      sys.dm_tran_locks tl
     LEFT JOIN sys.databases db
          ON db.[database_id] = tl.[resource_database_id]
     ORDER BY  tl.[request_session_id], db.[name], [LockResource];
     ROLLBACK TRAN;
     

     Returns (final three columns for “resource_subtype”, “resource_description”, and “request_owner_id” not shown):

     rqst.  Database  Lock              rqst.   rqst.  request  resource
     sesn.  Name      Resource          mode    type   status   type
     id
     51     TestDB    TestDB            S       LOCK   GRANT    DATABASE
     52     master    sysdbreg          Sch-S   LOCK   GRANT    OBJECT
     52     master    sysobjvalues      Sch-S   LOCK   GRANT    OBJECT
     52     tempdb    #Test_..._00001F  Sch-M   LOCK   GRANT    HOBT
     52     tempdb    #Test_..._00001F  Sch-M   LOCK   GRANT    OBJECT
     52     tempdb    PRIMARY           Sch-S   LOCK   GRANT    METADATA
     52     tempdb    sysallocunits     IX      LOCK   GRANT    OBJECT
     52     tempdb    sysallocunits     X       LOCK   GRANT    KEY
     52     tempdb    sysallocunits.nc  X       LOCK   GRANT    KEY
     52     tempdb    syscolpars        X       LOCK   GRANT    KEY
     ...
     

2. String_RemoveDiacritics (and “4k” version)
   • Removes accents and other diacritical marks from letters.
   • String is normalized such that letter characters have any accents, tildes, macrons, diaeresis, cedilla, etc removed (whether they are a part of the letter or combining characters added onto the letter)
   • Setting the second parameter, @UseCompatiblityForm, to “1” will break down characters into multiple basic characters if possible. For example: the single character ” ¼ ” broken into the three characters of ” 1 / 4 “
   • Examples:

     SELECT SQL#.String_RemoveDiacritics(N'sdfsd', 0);
     -- sdfsd (no change)
     SELECT SQL#.String_RemoveDiacritics(N'â', 0);
     -- a
     SELECT SQL#.String_RemoveDiacritics(N'â', 1);
     -- a (same change as above)
     DECLARE @Test NVARCHAR(20) = N'~Åa' + NCHAR(0x0344)
                              + NCHAR(0x0344) + NCHAR(0x0344) + N'~';
     SELECT @Test, SQL#.String_RemoveDiacritics(@Test, 0);
     -- ~Åä́̈́̈́~      ~Aa~
     -- (Multiple combining characters removed)
     SELECT SQL#.String_RemoveDiacritics(N'¼', 0);
     -- ¼ (no change)
     SELECT SQL#.String_RemoveDiacritics(N'¼', 1);
     -- 1 / 4 (broken into 3 characters)
     

3. Util_GetBase2Bits
   • Returns both the position and integer value of each bit that is set to “1” (i.e. True / Yes / On / Enabled).
   • Please see “Binary / Base2 / BitMask Notes” section at the bottom
   • Example:

     SELECT * FROM SQL#.Util_GetBase2Bits(N'0011');
     /*
     BitNum   BitVal
     1        1
     2        2
     */

4. Util_UnBitMask
   • Returns both the position and integer value of each bit that is included in the masked value.
   • This is not intended to work with negative numbers, but passing in a negative number does not error. However, it might not behave as expected (unless you expect to get back the bits of the Two’s Complement representation of the negative value).
   • Please see “Binary / Base2 / BitMask Notes” section at the bottom
   • Examples:

     SELECT * FROM SQL#.Util_UnBitMask(3);
     /*
     BitNum   BitVal
     1        1
     2        2
     */SELECT * FROM SQL#.Util_UnBitMask(4);
     /*
     BitNum   BitVal
     3        4
     */SELECT * FROM SQL#.Util_UnBitMask(18031994990493696);
     /*
     BitNum   BitVal
     33       4294967296
     45       17592186044416
     55       18014398509481984
     */

Also Added

“4k” versions of:

• String_PadBoth4k
• String_TrimChars4k
• String_TrimEnd4k
• String_TrimStart4k
• RegEx_CaptureGroupCaptures4k
• RegEx_CaptureGroups4k

New functionality in both Free and Full versions

1. Convert_Base2ToBase10
   • Converts a Base 2 value (a string of 1 – 64 “0”s and “1”s) into the equivalent integer (i.e. BIGINT) value.
   • Leading / left-most bits / characters not passed-in are assumed to be “0”.
   • Negative numbers use Two’s Complement method, and require that all 64 bits / characters must be passed in, with “1” as the leading / left-most digit
   • Please see “Binary / Base2 / BitMask Notes” section at the bottom
   • Examples:

     SELECT SQL#.Convert_Base2ToBase10(N'11');
     -- 3
     SELECT SQL#.Convert_Base2ToBase10(N'0011');
     -- 3
     SELECT SQL#.Convert_Base2ToBase10(
       N'0001000000000001000000000000000000000000000');
     -- 549890031616
     SELECT SQL#.Convert_Base2ToBase10(
     N'1111111110111111111011111111111100000000000000000000000000000000'
     );
     -- -18031994990493696
     

2. Convert_Base10ToBase2
   • Converts an integer value into the equivalent Base 2 value (a string of “0”s and “1”s).
   • Negative numbers use Two’s Complement method
   • Please see “Binary / Base2 / BitMask Notes” section at the bottom
   • Examples:

     SELECT SQL#.Convert_Base10ToBase2(3);
     -- 0000000000000000000000000000000000000000000000000000000000000011
     SELECT SQL#.Convert_Base10ToBase2(549890031616);
     -- 0000000000000000000000001000000000001000000000000000000000000000
     -- Bit #s: 4, 61, 63
     SELECT SQL#.Convert_Base10ToBase2(5764607523034234888);
     -- 0101000000000000000000000000000000000000000000000000000000001000
     SELECT SQL#.Convert_Base10ToBase2(-18031994990493696);
     -- 1111111110111111111011111111111100000000000000000000000000000000
     

Also Added

“4k” versions of:

• String_Trim4k
• RegEx_Escape4k
• RegEx_Index4k
• RegEx_Match4k
• RegEx_Matches4k
• RegEx_Split4k
• RegEx_Unescape4k

Improvements

• GENERAL
  • Greatly reduced size (by approx. 310 kb) of main SQL# Assembly by moving LookUp category into its own Assembly: SQL#.LookUps. This will improve initial load times and won’t waste much memory when not using the LookUp functions.
• Installation Script
  • Account for security changes related to SQL Server 2017 (i.e. “CLR strict security“) using a Certificate (flexible, clean) instead of the new “Trusted Assemblies” (inflexible, messy).
• Networking
  • Added explicit support for TLS 1.1 and TLS 1.2 protocols
  • Increased default “Connection Limit” for URIs to 20 from the .NET default of 2. This will reduce performance bottlenecks from concurrent access to the same URI.
• Twitter
  • All functions now have a concurrent connection limit of 25 instead of the .NET default of 2
  • Support sending of all UTF-8 characters
• INET_DownloadFile
  • Set “User-Agent” HTTP header (required by some sites)
  • Improved error message when SQL#.Network Assembly wasn’t at correct security level.
• INET_GetWebPages
  • Added support for “Keep-alive” HTTP header
  • Added support for “ConnectionLimit” pseudo-HTTP header (not case-sensitive) to set the URI’s Connection Limit. For example:

    SELECT *
    FROM   SQL#.INET_GetWebPages(
               'some_URI',
               1, 1, -1, -1, -1,
               N'ConnectionLIMIT=50',
               'post',
               N'hello there',
               N'auto'
           );
    

• String_Contains
  • @SearchValue input parameter is now NVARCHAR(MAX), something LIKE cannot do (please see “Overcome LIKE character length limitation” on DBA.StackExchange for details)

For the full list of changes, please see the Change Log

Binary / Base2 / BitMask Notes

The following notes apply to the 4 new functions: Util_GetBase2Bits (Full version only), Util_UnBitMask (Full version only), Convert_Base2ToBase10, and Convert_Base10ToBase2.

1. All values are 64-bit
   • Base10 type is BIGINT
   • Base2 type is a string of 1 – 64 “0”s and “1”s
2. BitNum(ber)
   • Identifies a particular bit
   • A value between 1 and 64
   • Bit “number” 1 is on the far right (i.e. “…001”), while “number” 64 is on the far left (i.e. “100…”)
3. BitVal(ue)
   • The bit “value”, as an integer, for the corresponding bit “number” (i.e. 2^(BitNum-1) )
   • Bit “number” 1 (far right) = 1, while “number” 63 (2^nd from far left) = 4611686018427387904
   • Bit “number” 64 (far left) is detected properly, but due to both the max value allowed in BIGINT and the left-most position indicating a negative value in Two’s Complement, its value is: -9223372036854775808
4. Negative values are represented using the Two’s Complement method

To look at it another way:

0101000000000000000000000000000000000000000000000000000000001000
^                                                              ^
|                                                              |
\_ Bit Number 64                                 Bit Number 1 _/
Bit Number 1's value = 1
Bit Number 2's value = 2
Bit Number 3's value = 4
Bit Number 4's value = 8
Base2 value 00000...00101 = Bit Number's 1 and 3 are "on".
Bit 1 value of "1" + Bit 3 value of "4" = 5.
Base10 value of 00000...00101 = 5.
Base10 value of 00000...00111 = 7 (added Bit 2 value of "2").
Base10 value of 00000...01000 = 8 (only Bit Number 4 is "on").