Skip to content

Strings

These functions are for working with strings.

fn_get_string_part_by_delimiter

This function gets a specified member of a delimited string.

CREATE FUNCTION [dbo].[fn_get_string_part_by_delimiter]
(
                @value AS     NVARCHAR(MAX), 
                @delimeter AS NVARCHAR(MAX), 
                @position AS  INT
)
RETURNS NVARCHAR(MAX)
AS
     BEGIN
         DECLARE @startpos AS INT;
         DECLARE @endpos AS INT;
         SET @endpos = -1;
         WHILE @position > 0
               AND @endpos != 0
             BEGIN
                 SET @startpos = @endpos + 1;
                 SET @endpos = CHARINDEX(@delimeter, @value, @startpos);
                 IF @position = 1
                     BEGIN
                         IF @endpos = 0
                             SET @endpos = LEN(@value) + 1;
                         RETURN RTRIM(LTRIM(SUBSTRING(@value, @startpos, @endpos - @startpos)));
                     END;
                 SET @position = @position - 1;
             END;
         RETURN NULL;
     END;
GO

fn_get_value_from_array_by_key

This function gets the specified value from an array using its key value.

CREATE FUNCTION [dbo].[fn_get_value_from_array_by_key](
               @array               NVARCHAR(MAX), 
               @key_enclosed_YN     NCHAR(1), 
               @array_separator     NCHAR(1), 
               @key_value_separator NCHAR(1), 
               @enclosure_character NCHAR(1), 
               @key                 NVARCHAR(255))
RETURNS NVARCHAR(MAX)
-- Requirements:    This function requires 'fn_split_strings', 'fn_replace_interior_character' and 'fn_get_string_part_by_delimiter'
AS
     BEGIN
         DECLARE @new_array AS NVARCHAR(MAX), 
                 @value AS     NVARCHAR(MAX), 
                 @temp AS      NVARCHAR(MAX);

         DECLARE @array_table TABLE
         (
                                    NUMBER INT, 
                                    Item   NVARCHAR(MAX), 
                                    [key]  NVARCHAR(MAX), 
                                    value  NVARCHAR(MAX)
         );

         SET @key_enclosed_YN = ISNULL(@key_enclosed_YN, N'N');

         IF @key_enclosed_YN NOT IN('Y', 'N')
             SET @key_enclosed_YN = N'N';

         IF @enclosure_character IS NOT NULL
             SET @new_array = dbo.fn_replace_interior_character(@array, @enclosure_character, @array_separator, N'|^|', N'|v|');
             ELSE
             SET @new_array = @array;

         INSERT INTO @array_table
                SELECT NUMBER, 
                       Item, 
                       NULL, 
                       NULL
                FROM dbo.fn_split_strings(@new_array, @array_separator);

         UPDATE @array_table
           SET 
               [key] = dbo.fn_get_string_part_by_delimiter(Item, @key_value_separator, 1), 
               value = dbo.fn_get_string_part_by_delimiter(Item, @key_value_separator, 2)
         FROM @array_table;

         IF @enclosure_character IS NOT NULL
            AND @key_enclosed_YN = 'N'
             UPDATE @array_table
               SET 
                   [key] = REPLACE(RIGHT([key], LEN([key]) - 1), @enclosure_character + @enclosure_character, @enclosure_character), 
                   value = REPLACE(LEFT(value, LEN(value) - 1), @enclosure_character + @enclosure_character, @enclosure_character)
             FROM @array_table;

         IF @enclosure_character IS NOT NULL
            AND @key_enclosed_YN = 'Y'
             UPDATE @array_table
               SET 
                   [key] = REPLACE(SUBSTRING([key], 2, LEN([key]) - 2), @enclosure_character + @enclosure_character, @enclosure_character)
             FROM @array_table;

         IF @new_array LIKE N'%' + @key + N'%'
             SET @value =
             (
                 SELECT value
                 FROM @array_table
                 WHERE [key] = @key
             );

         SET @value = CASE
                          WHEN RTRIM(LTRIM(@value)) = ''
                          THEN NULL
                          ELSE REPLACE(@value, N'|^|', @array_separator)
                      END;
         RETURN @value;
     END;
GO

fn_get_tablename_id_code

This function returns a non-guessable table id string.

-- =============================================
-- Author:      Michael D. Shook
-- Create date: 09/11/2020
-- Description: Creates a non-guessable table id string
-- Requires: vw_get_newid
-- =============================================
CREATE FUNCTION dbo.fn_get_tablename_code
(
)
RETURNS NVARCHAR(15)
AS
     BEGIN
         -- Declare the return variable here
         DECLARE @newCode NVARCHAR(15), 
                 @newId   NVARCHAR(63)

         -- Get a new ID to be used as the base for the code string
         SELECT @newId = value
         FROM vw_get_newid;

         -- Calculate new code from the new id 
         SELECT @newCode = RIGHT(CONVERT(NVARCHAR(31), HASHBYTES('MD5', CONVERT(NVARCHAR(63), @newId)), 2), 15);

         -- If the new code is already in the table, try again
         WHILE
         (
             SELECT tablename.code
             FROM tablename
             WHERE code = @newCode
         ) = @newCode
             BEGIN
                 -- Get a new ID to be used as the base for the ID string
                 SELECT @newId = value
                 FROM vw_get_newid;

                 -- Calculate new code from the new id 
                 SELECT @newCode = RIGHT(CONVERT(NVARCHAR(31), HASHBYTES('MD5', CONVERT(NVARCHAR(63), @newCode)), 2), 15);
             END
         -- Return the new code
         RETURN @newCode
     END

fn_proper_case

This function converts the input to proper case.

CREATE FUNCTION [dbo].[fn_proper_case](
                                          @text VARCHAR( MAX ) )
RETURNS VARCHAR( MAX ) AS
BEGIN
    DECLARE @Result VARCHAR( MAX );
    DECLARE @Reset BIT;
    DECLARE @i INT;
    DECLARE @c CHAR( 1 );
    IF @Text IS NULL
    RETURN NULL;
    SELECT @Reset = 1,
        @i =        1,
        @Result = '';

    WHILE @i <= LEN( @Text )
    SELECT @c = SUBSTRING( @Text, @i, 1 ),
        @Result = @Result +
        CASE
            WHEN @Reset = 1
            THEN UPPER( @c )
            ELSE LOWER( @c )
        END,
        @Reset =
        CASE
            WHEN @c LIKE '[a-zA-Z]'
            THEN 0
            ELSE 1
        END,
        @i = @i + 1;

    RETURN @Result;
END;
GO

fn_remove_non_alpha

This function removes any non-alpha characters from the input.

CREATE FUNCTION [dbo].[fn_remove_non_alpha](
                                                @strtext NVARCHAR( MAX ) )
RETURNS NVARCHAR( MAX ) AS
BEGIN
    DECLARE @result NVARCHAR( 255 );
    WHILE PATINDEX( '%[^A-Za-z]%', @strtext ) > 0
    BEGIN
        SET @strtext = STUFF( @strtext, PATINDEX( '%[^A-Za-z]%', @strtext ), 1, '' );
    END;
    SELECT @result = @strtext;

    RETURN @result;
END;
GO

fn_replace_interior_character

This function replaces a charcacter that is inside of enclosure characters.

CREATE FUNCTION [dbo].[fn_replace_interior_character](
               @List            NVARCHAR(MAX), 
               @EncloseChar     NCHAR(1), 
               @PatternChar     NCHAR(1), 
               @ReplaceChar     NVARCHAR(255), 
               @TempReplaceChar NVARCHAR(255))
RETURNS NVARCHAR(MAX)
AS
     BEGIN
         DECLARE @Result NVARCHAR(MAX)
         DECLARE @TableVariable TABLE
         (
                                      NUMBER INT, 
                                      Item   VARCHAR(MAX)
         )

         IF @TempReplaceChar = N'%'
             BEGIN
                 SET @TempReplaceChar = N'|v|'
             END
         SET @List = REPLACE(@List, @PatternChar, @TempReplaceChar)
         INSERT INTO @TableVariable
                SELECT NUMBER,
                       CASE
                           WHEN Item NOT LIKE N'%' + @TempReplaceChar
                                AND Item NOT LIKE @TempReplaceChar + N'%'
                           THEN replace(Item, @TempReplaceChar, @ReplaceChar)
                           ELSE Item
                       END AS Item
                FROM dbo.fn_split_strings(@List, @EncloseChar)

         SELECT @Result = ISNULL(@Result, '') + Item + @EncloseChar
         FROM @TableVariable
         ORDER BY NUMBER
         SELECT @Result = CASE
                              WHEN RIGHT(@List, 1) <> @EncloseChar
                              THEN SUBSTRING(@Result, 1, LEN(@Result) - LEN(@EncloseChar))
                              ELSE @Result
                          END
         SET @Result = REPLACE(@Result, @TempReplaceChar, @PatternChar)

         RETURN @Result
     END
GO

fn_split_strings

This function returns a recordset that contains all members of an input delimited string.

CREATE FUNCTION [dbo].[fn_split_strings]
(
                @List      NVARCHAR(MAX), 
                @Delimiter NVARCHAR(255)
)
RETURNS TABLE
AS
     RETURN
(
    SELECT NUMBER = ROW_NUMBER() OVER(
           ORDER BY NUMBER), 
           Item
    FROM
    (
        SELECT NUMBER, 
               Item = LTRIM(RTRIM(SUBSTRING(@List, NUMBER, CHARINDEX(@Delimiter, @List + @Delimiter, NUMBER) - NUMBER)))
        FROM
        (
            SELECT ROW_NUMBER() OVER(
                   ORDER BY s1.object_id)
            FROM sys.all_objects AS s1
                 CROSS APPLY sys.all_objects
        ) AS n(NUMBER)
        WHERE NUMBER <= CONVERT(INT, LEN(@List))
              AND SUBSTRING(@Delimiter + @List, NUMBER, LEN(@Delimiter)) = @Delimiter
    ) AS y
);
GO

vw_get_newid

This view returns a T-SQL ID to be used in a function.

-- =============================================
-- Author:      Michael D. Shook
-- Create date: 09/11/2020
-- Description: Returns a T-SQL ID to be used in a function
-- Requires: vw_get_newid
-- =============================================
CREATE VIEW dbo.vw_get_newid
AS
     SELECT NEWID() AS value
GO