Schema Z – String Operations

Schema z introduces objects manipulating string and simplifies your work, https://github.com/sqlnotes/sql/wiki/FunTricks#string-operations .

select z.fn_LettersOnly('Hello World 123')
-- HelloWorld
select z.fn_NumbersOnly('Hello World 123')
-- 123
  • z.fn_SplitString: This is an enhanced version of string_split. It allows multiple characters in the separator which is only one character supported in string_split. Recursive CTE is used Internally in this function, if the string contains over 100 separated values, MaxRecursion query option must be set to zero.
declare @LongString nvarchar(max) = replicate('a^^', 300)
select *
from z.fn_SplitString(@LongString, '^^')
option(MaxRecursion 0)
  • z.fn_SplitStringFixedLength: As its name, this function splits string by length. Recursive CTE is used Internally in this function, if the string contains over 100 fixed length values, MaxRecursion query option must be set to zero.
declare @LongString nvarchar(max) = replicate('a^^', 300)
select *
from z.fn_SplitStringFixedLength(@LongString, 3)
option(MaxRecursion 0)
select z.fn_SecondsToTimeString(98765)
-- 1.03:26:05
  • z.usp_PrintString This procedures helps print a string. It uses the PRINT command to output a long string. If the string contains multiple lines, it is printed line by line. For lines exceeding 4000 characters, the procedure prints 4000 characters at a time.
declare @str nvarchar(max) = replicate(cast('abc' as varchar(max)), 6000)
exec z.usp_PrintString @str

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.