String functions for T-Sql

Split

if object_id('Split') is not null
	drop function Split
go
create function Split(@s varchar(max))
returns @ret table (s varchar(max))
as
	begin
	insert @ret select s from SplitByString(@s, ',')
	return
	end
go

/*
select '/'+s+'/' from Split('this')
select '/'+s+'/' from Split('this,and')
select '/'+s+'/' from Split('this,and,that,and,the,other')
*/

SplitByCharset.sql

if object_id('SplitByCharset') is not null
	drop function SplitByCharset
go

create function SplitByCharset(@s varchar(max), @charset varchar(32))
returns @ret table (
	i int identity, s varchar(128)
	)
as
	begin
	/*
	**	- Synopsis -
	**	Splits a string by any of the characters in a given set,
	**	returning a table with its tokens.  Please note that the
	**	delimiters are swallowed.  Also, empty records are not
	**	returned.
	**
	**	- Syntax -
	**	@s: String to be split
	**	@charset: a string containing characters on which to split
	**
	**	- Marginalia -
	**	Author: Erick Calder <[email protected]>
	*/

	declare @i int = 0
	while @i <= len(@s)
		begin
		select  @i = @i + 1
		if charindex(substring(@s, @i, 1), @charset) > 0
			begin
			if left(@s, @i - 1) != ''
				insert @ret
				select ltrim(rtrim(left(@s, @i - 1)))
			set @s = substring(@s, @i + 1, len(@s))
			set @i = 0
			end
		end

	insert @ret select @s
	return
	end

go

-- exempli gratia ----------------------------------------------

/*
	select * from SplitByCharset('+1+2-3', '+-')
	select * from SplitByCharset('+1 +2 -3', '+-')
*/

SplitByNL.sql

if object_id('SplitByNL') is not null
	drop function SplitByNL
go
create function SplitByNL(@s varchar(max))
returns @ret table (s varchar(max))
as
	begin
	insert @ret select s from SplitByString(@s, char(13)+char(10)) where s != ''
	return
	end
go
/*
select '/'+s+'/' from SplitByNL('
this
and
that
and
the
other
')
*/

SplitByString.sql

if object_id('SplitByString') is not null
	drop function SplitByString
go

create function SplitByString(
	@s nvarchar(max)
,	@d nvarchar(10)
)
returns @ret table (
	i int identity, s nvarchar(max)
)
as
	/*
	**	- Synopsis -
	**	Splits a string delimited by a delimiter
	**	and returns a table with its tokens
	**
	**	- Syntax -
	**	@s: string that is to be split into a table
	**	@d: the delimiter the segments the string
	**
	**	- Marginalia -
	**	Author: Erick Calder <[email protected]>
	*/

	begin        
	declare @i int = 1
	while 1 = 1
		begin
		declare @j int = charindex(@d, @s, @i + 1)
		if @j = 0 break
		insert @ret select substring(@s, @i, @j - @i)
		set @i = @j + len(@d)
		end
	insert @ret select substring(@s, @i, len(@s))
	return        
	end

-- exempli gratia ------------------------------------------------------------

/*
	select * from SplitByString('this that', '/')
	select * from SplitByString('this--that', '--')
	select * from SplitByString('this/that/', '/')
*/

-- performance ---------------------------------------------------------------

/*
This function pays a certain cost for the use of nvarchars (4%) and the
inclusion of the identity column in the output table (15%).  applications
that care about better performance may remove these features for the
aforementioned gains

The code below can be used to generate performance metrics for @n number
of tokens in a string, where each token is @len characters in length.

declare @n int = 10000
,		@len int = 100
declare @s varchar(max) = ''
while @n > 1
	begin
	set @s = @s + replicate('x', @len) + ','
	set @n = @n - 1
	end
set @s = @s + replicate('x', @len)

declare @t1 as table (s varchar(max))
set @dt = getdate()
insert @t1 select s from SplitByString(@s)
print datediff(ms, @dt, getdate())
*/

SplitPairs.sql

if object_id('SplitPairs') is not null
	drop function SplitPairs
go

create function SplitPairs(@s varchar(4096))
returns @ret table (
	i int
,	name varchar(128)
,	value varchar(128)
)
as
	/*
	**	- Synopsis -
	**	Splits a comma delimited list of name-value pairs, returning
	**	a table with the values.  the pairs should be separated by
	**	equal signs.
	**
	**	- Syntax -
	**	@s: list of tuples
	**
	**	- Marginalia -
	**	Author: Erick Calder <[email protected]>
	*/

	begin
	insert	@ret
	select	i
	,	name = left(s, isnull(nullif(charindex('=', s) - 1, -1), len(s)))
	,	value = substring(s, charindex('=', s) + 1, len(s))
	from	Split(@s)

	update	@ret
	set	name = NULL
	where	name = value

	return
	end
go

-- exempli gratia ------------------------------------------------------------

/*
	select * from SplitPairs('x=this,y=that')
*/

StrClean.sql

if object_id('StrClean') is not null
	drop function StrClean
go

create function StrClean(@s varchar(max))
returns varchar(max)
as
	begin
	/*
	**	- Synopsis -
	**	Cleans a string of carriage returns, line feeds,
	**	tabs, and leading and trailing spaces.
	**
	**	- Syntax -
	**	@s: the string to clean
	**
	**	- Marginalia -
	**	Author: Erick Calder <[email protected]>
	*/
	set @s = replace(@s, char(13), '') -- carriage returns
	set @s = replace(@s, char(10), '') -- line feeds
	set @s = replace(@s, char(9), '') -- tabs
	return ltrim(rtrim(@s))
	end
go

-- exempli gratia ------------------------------------------------------------

/*
	select dbo.StrClean(' this	that ')
*/

StrNumeric.sql

if object_id('StrNumeric') is not null
	drop function StrNumeric
go

create function dbo.udfStrToNumeric (@s varchar(32))
returns float
as
	/*
	**	- Synopsis -
	**	This function strips non-numeric characters from
	**	a string allowing for conversion of percetages ("40.2%")
	**	spreads ("L+50") and other such variations
	**
	**	- Marginalia -
	**	Author: Erick Calder <[email protected]>
	*/

	begin
	declare	@i int = 1
	,	@ret varchar(32) = ''
	,	@c char(1)

	while @i <= len(@s)
		begin
		select @c = substring(@s, @i, 1)
		select @ret = @ret + @c
		where IsNumeric(@c) = 1 OR @c IN ('-', '.')

		select @i = @i + 1
		end

	return convert(float, @ret)
	end
go

-- exempli gratia -------------------------------------------------------------

/*
	select 'X-30.2', dbo.StrToNumeric('X-30.2')
	select '45.2%', dbo.StrToNumeric('45.2%')
	select 'L+300', dbo.StrToNumeric('L+300')
*/

StrQuote.sql

if object_id('StrQuote') is not null
	drop function StrQuote
go

create function dbo.StrQuote(@s varchar(max))
returns varchar(max)
as
	begin
	/*
	**	- Synopsis -
	**	Quotes a string
	**
	**	- Marginalia -
	**	Author: Erick Calder <[email protected]>
	*/

	return	char(39)
	+	replace(@s, char(39), char(39) + char(39))
	+	char(39)
	end
go

-- exempli gratia -------------------------------------------------------------

/*
	select dbo.StrQuote('Rick place')
	,	dbo.StrQuote('Rick''s place')
*/

StrToAscii.sql

if object_id('StrToAscii') is not null
	drop function StrToAscii
go
create function StrToAscii(@s varchar(max))
returns varchar(max)
as
	begin
	/*
	**	- Synopsis -
	**	Returns a sring consisting of the characters
	**	and their ordinal values in the string passed in
	**
	**	- Marginalia -
	**	Author: Erick Calder <[email protected]>
	*/

	declare	@i int = 1
	,	@ret varchar(max) = ''

	while @i <= len(@s)
		begin
		select @ret = @ret
		+ substring(@s, @i, 1)
		+ '=' + convert(varchar, ascii(substring(@s, @i, 1)))
		+ ' '
		select @i = @i + 1
		end

	return @ret
	end
go

-- exempli gratia -------------------------------------------------------------

/*
	select dbo.StrToAscii('sample text')
*/

StrToken.sql

if object_id('StrToken') is not null
	drop function StrToken
go

create function StrToken (@s varchar(max), @n int, @dc char(1))
returns varchar(max)
as
	/*
	**	- Synopsis -
	**	Used to extract the nth token from a string segmented
	**	by @dc (delimiting character)
	**
	**	- Marginalia -
	**	Author: Erick Calder <[email protected]>
	*/

	begin
	return (select s from SplitByString(@s, @dc) where i = @n)
	end
go

-- exempli gratia -------------------------------------------------------------

/*
	select dbo.StrToken('der/herrgott/gibt', 2, '/')
*/

StrTokenNbr.sql

if object_id('StrTokenNbr') is not null
	drop function StrTokenNbr
go

create function StrTokenNbr(
	@s varchar(max), @tok varchar(max), @dc char(1)
	)
returns int
as
	/*
	**	- Synopsis -
	**	Returns the position of @tok within a @dc separated string @s
	**	If the last token in @s is an asterisk, @tok will match it,
	**	returning its position.  This is useful for doing /else/ type
	**	matches when sorting e.g.
	**		ORDER BY dbo.udfStrTokenNbr('bid,mean,ask', code, ',')
	**	cf. udfSecurityYTW() for use
	**
	**	- Marginalia -
	**	Author: Erick Calder <[email protected]>
	*/

	begin
	declare	@x table (i int, s varchar(max))
	insert	@x select i, s from SplitByString(@s, @dc)

	declare	@ret int
	select @ret = i from @x where s = @tok
	if @ret is null and right(@s, 1) = '*'
		select @ret = count(*) from @x

	return @ret
	end
go

-- exempli gratia -------------------------------------------------------------

/*
	select dbo.StrTokenNbr('besser|spaet|als|nie', 'als', '|')
	select dbo.StrTokenNbr('besser|spaet|als|nie', 'nix', '|')
	select dbo.StrTokenNbr('besser|spaet|als|nie|*', 'nix', '|')
*/