String

chm.SplitString

This TVF function splits an string based on the given separator and returns the result as a table.

		  CREATE FUNCTION [chm].[SplitString]
(
	@data       NVARCHAR(MAX),
	@separator  NVARCHAR(MAX),
	@encoding   NVARCHAR(20)
)
RETURNS 
	TABLE
	(
		[id]    INT            NULL,
		[value] NVARCHAR (MAX) NULL
	)
		

Example:

		  select * from chm.SplitString('10,20,30', ',', 'default')
		

result:

chm.Transplit

This TVF function splits an string based on the given separator, applies requested transform on each part and returns the result as a table.

		  CREATE FUNCTION [chm].[Transplit]
(
	@data       NVARCHAR(MAX),
	@separator  NVARCHAR(MAX),
	@transforms NVARCHAR(500),
	@encoding   NVARCHAR(20)
)
RETURNS 
	TABLE
	(
		[id]    INT            NULL,
		[value] NVARCHAR (MAX) NULL
	)
		

Parameters:

• @data: Input data
• @separator: Separator by which items in @data are merged with.
• @transforms: A comma-separated list of transforms that should be applied on each item when splitting. Possible values are:
  • IgnoreEmpty or ie
  • IgnoreWhitespace or iw
  • Trim or t
  • RTrim or rt
  • LTrim or lt
  • ToLower or l
  • ToUpper or u
  • ChangeCase or cc
  • PascalCase or psc
  • CamelCase or cml
  • Reverse or r
  • HtmlEncode or he
  • HtmlDecode or hd
  • FullUrlEncode or fue
  • UrlEncode or ue
  • FullUrlDecode or fud
  • UrlDecode or ud
  • Base64Encode or be
  • Base64Decode or bd
• @encoding: Input encoding Example:

		  select * from chm.Transplit('Green, Red,, , Blue', ',', 'lower,trim,ignoreEmpty', '')
		

result:

Transformations in this example could also be briefed:

		  select * from chm.Transplit('Green, Red,, , Blue', ',', 'l,t,ie', '')
		

chm.CharCount

This function returns how many times a character exists inside an string.

		  CREATE FUNCTION [chm].[CharCount]
(
	@data   NVARCHAR(MAX),
	@ch     NCHAR(1)
)
RETURNS INT
		

Example:

		  select chm.CharCount('To be or not to be', 't') -- 3
		

chm.Length

This function returns length of a string. This function is provided as a single-source-of-truth for returning length of a string. Compared to intrinsic T-SQL LEN and DATALENGTH where there are nuances on how they work, chm.Length returns a single and correct length for a given input.

		  CREATE FUNCTION [chm].[Length]
(
	@data NVARCHAR(MAX)
)
RETURNS INT
		

Example:

		  declare @input nvarchar(100)
	
set @input = 'Test  '
select len(@input) as [Len], datalength(@input) as [DataLength], chm.Length(@input) as [Length]
		

Result:

chm.StringCount

This function returns number of occurances of a given string inside another string.

		  CREATE FUNCTION [chm].[StringCount]
(
	@data       NVARCHAR(MAX),
	@keyword    NVARCHAR(MAX),
	@comparison NVARCHAR(20)
)
RETURNS INT
		

@comparison parameter specifies how @keyword is compared during look up inside @data. Its possible values are described here.

Example:

		  select chm.StringCount('To be or not to be', 'to', '') -- 2
select chm.StringCount('To be or not to be', 'to', 'ordinal') -- 1
		

chm.ChangeCase

This functions inverts case-sensitivity of characters of the input.

		  CREATE FUNCTION [chm].[ChangeCase]
(
	@data NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
		

Example:

		  select chm.ChangeCase('Hello World')    -- hELLO wORLD
		

chm.PascalCase

This functions turns an input string into pascal-case.

      CREATE FUNCTION [chm].[PascalCase]
(
    @data NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
    

Example:

      select chm.PascalCase('how are you?')    -- How Are You?
    

chm.CamelCase

This functions turns an input string into camel-case.

		  CREATE FUNCTION [chm].[CamelCase]
(
	@data NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
		

Example:

		  select chm.CamelCase('IgnoreCase')    -- ignoreCase
		

chm.PadLeft

This functions carries out the same thing as String.PadLeft() in .NET Framework on strings. It receives a string, a width, a filler character and appends required filler characters from left-side of the given string to satisfy requested width.

		  CREATE FUNCTION [chm].[PadLeft]
(
	@data       NVARCHAR(MAX),
	@totalWidth INT,
	@ch NCHAR(1)
)
RETURNS NVARCHAR (MAX)
		

Example:

		  select chm.PadLeft('123', '8','-')
		

Output:

		  -----123
		

chm.PadRight

This functions carries out the same thing as String.PadRight() in .NET Framework on strings. It receives a string, a width, a filler character and appends required filler characters from right-side of the given string to satisfy requested width.

		  CREATE FUNCTION [chm].[PadRight]
(
	@data       NVARCHAR(MAX),
	@totalWidth INT,
	@ch NCHAR(1)
)
RETURNS NVARCHAR (MAX)
		

Example:

		  select chm.PadRight('123', '8','-')
		

Output:

		  123-----
		

chm.StringCompare

This function compares two strings based on the requested comparison and returns an integer as a result.

		  CREATE FUNCTION [chm].[StringCompare]
(
	@strA       NVARCHAR(MAX),
	@strB       NVARCHAR(MAX),
	@comparison NVARCHAR(MAX)
)
RETURNS INT
		

Possible values for @comparison parameter is described here. Result meaning:

• < 0: @strA is less than @strB
• 0: equal
• > 0: @strA is greater than @strB

Example:

		  select chm.StringCompare('Red', 'red', 'o')	-- Ordinal: -32
select chm.StringCompare('Red', 'red', 'oic') -- OrdinalIgnoreCase: 0
		

chm.StringTransform

This function performs a list of transformations on a given input string.

		  CREATE FUNCTION [chm].[StringTransform]
(
	@data       NVARCHAR(MAX),
	@transforms NVARCHAR(500),
	@encoding   NVARCHAR(20)
)
RETURNS NVARCHAR (MAX)
		

Parameters:

• @data: Input string.
• @transforms: Comma-separated list of transformation to be applied of @data
• @encoding: Character encoding of input string. Its possible values are described here.

Example:

		  select chm.StringTransform('  .NET framework   ', 'lower,trim', '')     -- .net framework
		

chm.IsEmpty

This function returns true if the given input string is either NULL, empty string or an all-whitespace character string.

		  CREATE FUNCTION [chm].[IsEmpty]
(
	@data NVARCHAR(MAX)
)
RETURNS BIT
		

Example:

		  select chm.IsEmpty(null)    -- 1
select chm.IsEmpty('')    -- 1
select chm.IsEmpty('   ')    -- 1
select chm.IsEmpty(N'
')    -- 1
select chm.IsEmpty(char(10) + char(13))    -- 1
select chm.IsEmpty('   -')    -- 0
		

chm.IsNullOrEmpty

This function returns true if the given input string is either NULL or empty.

      CREATE FUNCTION [chm].[IsEmpty]
(
    @data NVARCHAR(MAX)
)
RETURNS BIT
    

Example:

      select chm.IsEmpty(null)    -- 1
select chm.IsEmpty('')    -- 1
select chm.IsEmpty('   ')    -- 0
select chm.IsEmpty(N'
')    -- 0
select chm.IsEmpty(char(10) + char(13))    -- 0
select chm.IsEmpty('   -')    -- 0
    

chm.IsNullOrWhiteSpace

This function returns true if the given input string is either NULL, empty or an all-whitespace characters string. It acts the same as IsEmpty.

		  CREATE FUNCTION [chm].[IsEmpty]
(
	@data NVARCHAR(MAX)
)
RETURNS BIT
		

Example:

		  select chm.IsNullOrWhiteSpace(null)    -- 1
select chm.IsNullOrWhiteSpace('')    -- 1
select chm.IsNullOrWhiteSpace('   ')    -- 0
select chm.IsNullOrWhiteSpace(N'
')    -- 0
select chm.IsNullOrWhiteSpace(char(10) + char(13))    -- 0
select chm.IsNullOrWhiteSpace('   -')    -- 0
		

chm.SelectOf

This function checks whether a given value exists inside a list of values and returns it or a default value if it does not. It is also able to apply a transformation on the list of values before looking up for the given string.

		  CREATE FUNCTION [chm].[SelectOf]
(
	@s              NVARCHAR(MAX),
	@values         NVARCHAR(MAX),
	@separator      NVARCHAR(MAX),
	@transforms     NVARCHAR(500),
	@defaultValue   NVARCHAR(MAX),
	@encoding       NVARCHAR(20)
)
RETURNS NVARCHAR (MAX)
		

Parameters:

• @s: Given input
• @values: List of values
• @separator: Separator string by which items are joined together.
• @transforms: Comma-separated list of transformations to be applied on list of values.
• @defaultValue: default value to return if @s was not found inside @values.
• @encoding: Character encoding of @s and @values. Its possible values is described here.

Example:

		  declare @color varchar(20)
	
set @color = 'green'

select chm.SelectOf(@color, 'Red, Green, Blue, Yellow, Black, Pink', ',', 'lower,trim', 'black', '')        -- green

set @color = 'cyan'

select chm.SelectOf(@color, 'Red, Green, Blue, Yellow, Black, Pink', ',', 'lower,trim', 'black', '')        -- black
		

chm.StringToByteArray

This function converts an string into an array of bytes.

		  CREATE FUNCTION [chm].[StringToByteArray]
(
	@data       NVARCHAR(MAX),
	@encoding   NVARCHAR(MAX)
)
RETURNS 
		TABLE (
		[id]    INT     NULL,
		[value] TINYINT NULL)
		

Parameters:

• @data: Given string.
• @encoding: Character encoding of @data. Its possible values is described here.

Example:

		  select * from chm.StringToByteArray('Red', '')
		

Result:

chm.LastIndexOf

This function returns last index of a given input inside of an string.

		  CREATE FUNCTION [chm].[LastIndexOf]
(
	@expression NVARCHAR(MAX),
	@str        NVARCHAR(MAX),
	@startIndex INT,
	@comparison NVARCHAR(MAX)
)
RETURNS INT
		

Parameters:

• @expression: string to be searched.
• @str: string inside of which @expression is searched.
• @startIndex: Last index from which search should be performed. This is a 1-based index not zero-based index so that it conforms to CHARINDEX().
• @comparison: compare type. possible values are described here.

Example:

		  declare @s varchar(100) = 'to be or not to be'
declare @i int = len(@s)

while 1 = 1
begin
	set @i = chm.LastIndexOf('be', @s, @i, 'oic')

	if @i < 0
		break
	
	print @i
end
		

Output:

		  17
4