Last active
December 12, 2015 09:19
-
-
Save doug-wade/4750613 to your computer and use it in GitHub Desktop.
A function that trims whitespace from strings
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment