A function that trims whitespace from strings

isNullOrWhiteSpace

if exists 
(
	select *
	from sys.objects
	where objects.object_id = OBJECT_ID(N'[dbo].[isNullOrWhiteSpace]')
)
begin
	drop function dbo.isNullOrWhiteSpace
end
go

set ansi_nulls on
go

set quoted_identifier on
go

create function dbo.isNullOrWhiteSpace(@stringToTest as varchar(max))
returns bit
as
/*here's what's up
Written by: Doug Wade
On: 2013-03-04
Does: checks to see if a string is nothing but whitespace, or is null.
*/
	begin
	if len(dbo.trim(@stringToTest)) <= 0
		begin
		return 1
		end
	else
		begin
		return 0
		end
	return 0 
	end
go

trim

if exists (
	select * 
	from sys.objects 
	where object_id = OBJECT_ID(N'[dbo].[trim]') 
	and type in  (N'FN', N'IF', N'TF', N'FS', N'FT')
		  )
begin
	drop function [dbo].[trim]
end
go

set ansi_nulls on
go

set ansi_padding on
go

set quoted_identifier on
go

/*here's what's up
Written by: Doug Wade
On: 2013-03-04
Does: Trims leading and trailing spaces, as well as carriage return, line feed, and tabs.
*/
create function [dbo].[trim] (@string varchar(8000))
returns varchar(8000)
as
begin
if charindex(CHAR(13),@string) > 0 or charindex(CHAR(10),@string) > 0 or charindex(char(9),@string) > 0
begin
	declare @done bit = 0
	--first we'll strip the characters from the front of the string
	while @done = 0
	begin
		if len(@string) <= 0 
		begin
			return ''
		end
		--if the first character is a line feed, carriage return, tab, or space...
		if substring(@string,1,1) in (char(13),char(10),char(9),char(32))
		begin
			--... trim the first character off the string.
			set @string = substring(@string,2,len(@string) - 1)
		end
		else
		begin
			set @done = 1
		end
	end
	--then reset the done flag so we don't need to allocate an extra variable, but strip more than one character
	set @done = 0
	while @done = 0
	begin
		if len(@string) <= 0 
		begin
			return ''
		end
		--if the first character is a line feed, carriage return, tab, or space...
		if substring(@string,len(@string),1) in (char(13),char(10),char(9),char(32))
		begin
			--... trim the last character off the string.
			set @string = substring(@string,1,len(@string) - 1)
		end
		else
		begin
			set @done = 1
		end
	end
	return @string
end
--doesn't contain other whitespace, so we can use the built-in functions for speed (majority of cases) 
else
begin
	return ltrim(rtrim(@string))
end
--in case of emergencies or failures
return null
end

go