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