In this workbench, we’ll show you some fairly simple string User-Functions. Rather than invent the ones we like, we’re going to take a different angle and implement the Python string functions, as much as we can. Plenty of examples, and some programming tricks too!<\/p>\n
Sometimes, when developing a SQL Server application, you start to want to do some string manipulation. For the beginner, the SQL String functions seem pretty unpreposessing \u2013 there seems little there in the same league as what’s available in Python.<\/p>\n
The difference is more one of style. The basic TSQL functions have great power but it isn’t always obvious, from looking at the code, what they are doing. Nobody would attempt to argue that the famous When setting out a database project, it is always best to start out with a basic toolkit of elementary string user-functions that make your code readable. It is only when you hit a particular performance problem that you’d need to use the built-in functions rather than your own. In the heat of a team development, things generally seem to go better if the basic string user-functions are there, and ready to use.<\/p>\n For no particularly good reason, we like to use the PHP and Python string functions, adapted for SQL Server use. We’ve already described some of the routines we borrowed from PHP in The TSQL String Array Workbench<\/a>.<\/p>\n The Python ones we use are…<\/p>\n Return a copy of the string with only its first character capitalized.<\/p>\n Returns a copy of Returns the number of occurrences of substring Return non-zero if the string ends with the specified suffix, otherwise return Returns a copy of Returns Non-Zero if all characters in Returns non-zero if all characters in Returns non-zero if all characters in Returns non-zero if all characters in s are lowercase characters, Return Returns non-zero if all characters in s are whitespace characters, Returns a copy of Return a copy of the string with leading characters removed. The chars argument is a string specifying the set of characters to be removed. If omitted or Return the highest index in the string where substring sub is found, such that sub is contained within s[start,end]. Optional arguments start and end are interpreted as in slice notation. Return -1 on failure.<\/p>\n Returns a copy of Return a copy of the string with trailing characters removed. The chars argument is a string specifying the set of characters to be removed. If omitted or None, the chars argument defaults to removing whitespace. The chars argument is not a suffix; rather, all combinations of its values are stripped:<\/p>\n Return a copy of the string with the leading and trailing characters removed. The chars argument is a string specifying the set of characters to be removed. If omitted or Return a copy of the string with uppercase characters converted to lowercase and vice versa.<\/p>\n Returns a titlecased copy of Return the numeric string left-filled with zeros in a string of length All finished? Well, no, actually. We’ve now reached the point where we can tie in the String Array work we did in the TSQL String Array Workbench<\/a> and implement the handful of Python functions that use tuples and lists. However, that’s certainly enough for one sitting, and the more complicated functions will take some explaining, so we’ll meet again for Episode 2 of the String User Function Workbench<\/a><\/b>.<\/p>\n","protected":false},"excerpt":{"rendered":" Robyn and Phil go back to basics and hammer out some basic String-handling User Functions in TSQL, based on Python examples. Plenty of sample code, and TSQL programming tricks.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4242,4150,4252,4190,4857,4460],"coauthors":[6813,6814],"class_list":["post-366","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-basics","tag-sql","tag-t-sql-programming","tag-tsql","tag-tsql-string-user-function-udf-examples-sample-code","tag-workbench"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/366","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=366"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/366\/revisions"}],"predecessor-version":[{"id":77215,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/366\/revisions\/77215"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=366"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=366"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=366"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=366"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}STUFF<\/code> function is intuitive! (Robyn documented the basic string functions in her Robyn Page’s SQL Server String Manipulation Workbench<\/a>.)<\/p>\nCapitalize<\/b> string Function<\/h3>\n
IF OBJECT_ID(N'Capitalize') IS NOT NULL\r\n DROP FUNCTION Capitalize\r\nGO\r\nCREATE FUNCTION [dbo].[Capitalize] (@string VARCHAR(MAX))\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n DECLARE @FirstAsciiChar INT\r\n\r\n SELECT @FirstAsciiChar =\r\n PATINDEX('%[^a-zA-Z][abcdefghijklmnopqurstuvwxyz]%', ' '\r\n + @string COLLATE Latin1_General_CS_AI)\r\n IF @FirstAsciiChar > 0\r\n SELECT @String = STUFF(@String,\r\n @FirstAsciiChar,\r\n 1,\r\n UPPER(SUBSTRING(@String, @FirstAsciiChar, 1)))\r\n RETURN @string\r\n END\r\nGO\r\nSELECT dbo.capitalize('god save her majesty')\r\n--God save her majesty\r\nGO\r\n<\/pre>\nCentre<\/b> string Function<\/h3>\n
@String <\/code>centered in a string of length @width<\/code>, surrounded by the appropriate number of @fillChar<\/code> characters<\/p>\nIF OBJECT_ID(N'Center') IS NOT NULL\r\n DROP FUNCTION Center\r\nGO\r\nCREATE FUNCTION Center\r\n (\r\n @String VARCHAR(MAX),\r\n @width INT,\r\n @fillchar VARCHAR(10) = '''' )\r\n\/*\r\ne.g.\r\n\r\nselect dbo.center('Help me please',100,'*')\r\nselect dbo.center('error',100,'*!=')\r\nselect dbo.center('error',null,null)\r\nselect dbo.center(null,null,null)\r\n\r\n*\/\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n IF @string IS NULL\r\n RETURN NULL\r\n DECLARE @LenString INT\r\n DECLARE @LenResult INT\r\n-- Declare the return variable here\r\n SELECT @lenString = LEN(@String), @Fillchar = COALESCE(@Fillchar, ' '), @width = COALESCE(@Width, LEN(@String) * 2)\r\n SELECT @lenResult = CASE WHEN @LenString > @Width THEN @LenString\r\n ELSE @width\r\n END\r\n RETURN STUFF(REPLICATE(@fillchar, @lenResult \/ LEN(REPLACE(@FillChar, ' ', '|'))), (@LenResult - LEN(@String) + 2) \/ 2, @lenString, @String)\r\n END\r\nGO\r\n\r\nGO\r\n<\/pre>\nCount substring in string Function<\/h3>\n
sub<\/code> in string s<\/code>. Allows you to specifying the start and end position of the search.<\/p>\nIF OBJECT_ID(N'Count') IS NOT NULL\r\n DROP FUNCTION [Count]\r\nGO\r\nCREATE FUNCTION dbo.[Count]\r\n (\r\n @string VARCHAR(MAX),\r\n @Sub VARCHAR(MAX),\r\n @start INT = NULL,\r\n @end INT = NULL\r\n )\r\nRETURNS INT\r\nAS BEGIN\r\n DECLARE @more INT\r\n DECLARE @count INT\r\n IF @string = NULL\r\n RETURN NULL\r\n SELECT @count = 0, @more = 1, @Start = COALESCE(@Start, 1), @end = COALESCE(@end, LEN(@string))\r\n SELECT @end = CASE WHEN @end > LEN(@string) THEN LEN(@string)\r\n ELSE @end\r\n END, @Start = CASE WHEN @start > LEN(@string) THEN LEN(@string)\r\n ELSE @start\r\n END\r\n WHILE @more <> 0\r\n BEGIN\r\n SELECT @more = PATINDEX('%' + @sub + '%', SUBSTRING(@string, @Start, @End - @start + 1))\r\n IF @more > 0\r\n SELECT @Start = @Start + @more, @count = @count + 1\r\n IF @start >= @End\r\n SELECT @more = 0\r\n END\r\n RETURN @count\r\n END\r\nGO\r\n\r\nSELECT dbo.COUNT('The artistic temperament is something that afflicts amateurs', '[^a-z][a-z]', NULL, NULL)\r\n--wordcount (not include first word) 4\r\nSELECT dbo.COUNT('IT salesmen are sometimes so intellectually simple as to hide in packing cases or pretend to be their own aunts.', '[aeiou]', NULL, NULL)\r\n--37 vowels\r\nSELECT dbo.COUNT('45667892398', '8', NULL, NULL)\r\n--2\r\nSELECT dbo.COUNT('if something is worth doing, it is worth doing badly', 'worth doing', 17, 46)\r\n--2\r\n\r\nGO\r\n<\/pre>\nEndsWith<\/b> string Function<\/h3>\n
False<\/code>. Suffix can also be a list of suffixes to look for. With optional start, test beginning at that position. With optional end, stop comparing at that position.<\/p>\nIF OBJECT_ID(N'EndsWith') IS NOT NULL\r\n DROP FUNCTION EndsWith\r\nGO\r\nCREATE FUNCTION EndsWith\r\n (\r\n @String VARCHAR(MAX),\r\n @suffix VARCHAR(MAX),\r\n @start INT = NULL,\r\n @end INT = NULL\r\n )\r\nRETURNS INT\r\nAS BEGIN\r\n SELECT @Start = COALESCE(@Start, 1),\r\n @End = COALESCE(@End, LEN(@String))\r\n IF @string IS NULL OR @suffix IS NULL\r\n RETURN NULL\r\n SELECT @end = CASE WHEN @end > LEN(@string)\r\n THEN LEN(@string)\r\n ELSE @end\r\n END,\r\n @Start = CASE WHEN @start > LEN(@string)\r\n THEN LEN(@string)\r\n ELSE @start\r\n END\r\n\r\n RETURN PATINDEX('%' + @suffix,\r\n SUBSTRING(@string,\r\n @Start,\r\n @End - @start + 1))\r\n END\r\nGO\r\n\r\nSELECT dbo.endswith('Silence is the unbearable repartee', 'tee',\r\n DEFAULT, DEFAULT)\r\nSELECT dbo.endswith('a yawn is a silent shout', 'shout', 3, DEFAULT)\r\nSELECT dbo.endswith('Most people are struck by inspired ideas, but they generally pick themselves up and hurry off as if nothing had happened', 'inspired', 3,\r\n 35)\r\nSELECT dbo.endswith('Prudent dullness marked him out as project manager.', '[.;:,]', DEFAULT, DEFAULT)\r\n<\/pre>\nExpand Tabs<\/b> in a string<\/h3>\n
@String<\/code> where all tab characters are expanded using spaces.<\/p>\nIF OBJECT_ID(N'ExpandTabs') IS NOT NULL\r\n DROP FUNCTION ExpandTabs\r\nGO\r\nCREATE FUNCTION dbo.[ExpandTabs]\r\n (\r\n @String VARCHAR(MAX),\r\n @tabsize INT = NULL\r\n )\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n SELECT @tabsize = COALESCE(@tabsize, 4)\r\n IF @string IS NULL\r\n RETURN NULL\r\n DECLARE @OriginalString VARCHAR(MAX),\r\n @DetabbifiedString VARCHAR(MAX),\r\n @Column INT,\r\n @Newline INT\r\n SELECT @OriginalString = @String, @DeTabbifiedString = '',\r\n @NewLine = 1, @Column = 1\r\n WHILE PATINDEX('%[' + CHAR(9) + CHAR(10) + ']%', @OriginalString) > 0\r\n BEGIN--do we need to expand tabs?\r\n IF CHARINDEX(CHAR(9), @OriginalString + CHAR(9))\r\n > CHARINDEX(CHAR(10), @OriginalString + CHAR(10))\r\n BEGIN--we have to deal with a CR\r\n SELECT @NewLine = 1, @Column = 1,\r\n @DeTabbifiedString = @DeTabbifiedString\r\n + SUBSTRING(@OriginalString,\r\n 1,\r\n CHARINDEX(CHAR(10), @OriginalString)),\r\n @OriginalString = STUFF(@OriginalString, 1,\r\n CHARINDEX(CHAR(10),\r\n @OriginalString),'')\r\n END\r\n ELSE\r\n BEGIN--de-tabbifying\r\n SELECT @Column = @column\r\n + CHARINDEX(CHAR(9),\r\n @OriginalString + CHAR(9)) - 1,\r\n @DeTabbifiedString = @DeTabbifiedString\r\n + SUBSTRING(@OriginalString, 1,\r\n CHARINDEX(CHAR(9),@OriginalString)\r\n - 1)\r\n SELECT @DeTabbifiedString = @DeTabbifiedString\r\n + SPACE(@TabSize - (@column % @TabSize)),\r\n @OriginalString = STUFF(@OriginalString, 1,\r\n CHARINDEX(CHAR(09),\r\n @OriginalString),\r\n '')\r\n SELECT @Column = @Column + (@TabSize - (@column % @TabSize))\r\n END\r\n END\r\n RETURN @DeTabbifiedString + @Originalstring\r\n END\r\nGO\r\n<\/pre>\nIsAlNum<\/b> string Function<\/h3>\n
@String<\/code> are alphanumeric, 0 otherwise.<\/p>\nIF OBJECT_ID(N'IsAlnum') IS NOT NULL\r\n DROP FUNCTION IsAlnum\r\nGO\r\nCREATE FUNCTION dbo.[IsAlnum] (@string VARCHAR(MAX)) \r\n\/*\r\nSelect dbo.isalnum('how many times must I tell you')\r\nSelect dbo.isalnum('345rtp')\r\nSelect dbo.isalnum('co10?')\r\n*\/\r\nRETURNS INT\r\nAS BEGIN\r\n RETURN CASE WHEN PATINDEX('%[^a-zA-Z0-9]%', @string) > 0 THEN 0\r\n ELSE 1\r\n END\r\n END\r\nGO\r\n<\/pre>\nIsAlpha<\/b> string Function<\/h3>\n
@String<\/code> are alphabetic, 0<\/code> otherwise.<\/p>\nIF OBJECT_ID(N'IsAlpha') IS NOT NULL\r\n DROP FUNCTION IsAlpha\r\nGO\r\nCREATE FUNCTION dbo.IsAlpha (@string VARCHAR(MAX)) \r\n--Select dbo.isalpha('how many times must I tell you')\r\n--Select dbo.isalpha('SQLsequel')\r\n--Select dbo.isalpha('co10')\r\nRETURNS INT\r\nAS BEGIN\r\n RETURN CASE WHEN PATINDEX('%[^a-zA-Z]%', @string) > 0 THEN 0\r\n ELSE 1\r\n END\r\n END\r\nGO\r\n<\/pre>\nIsDigit<\/b> string Function<\/h3>\n
@string<\/code> are digit (numeric) characters, 0<\/code> otherwise.<\/p>\nIF OBJECT_ID(N'IsDigit') IS NOT NULL\r\n DROP FUNCTION IsDigit\r\nGO\r\nCREATE FUNCTION dbo.[IsDigit] (@string VARCHAR(MAX)) \r\n\/*\r\nSelect dbo.isdigit('how many times must I tell you')\r\nSelect dbo.isdigit('294856')\r\nSelect dbo.isdigit('569.45')\r\n*\/\r\nRETURNS INT\r\nAS BEGIN\r\n RETURN CASE WHEN PATINDEX('%[^0-9]%', @string) > 0 THEN 0\r\n ELSE 1\r\n END\r\n END\r\nGO\r\n<\/pre>\nIsLower<\/b> string Function<\/h3>\n
0<\/code> otherwise.<\/p>\nIF OBJECT_ID(N'IsLower') IS NOT NULL\r\n DROP FUNCTION IsLower\r\nGO\r\nCREATE FUNCTION dbo.[IsLower] (@string VARCHAR(MAX)) \r\n\/*\r\nSelect dbo.islower('how many times must i tell you')\r\nSelect dbo.islower('how many times must I tell you')\r\nSelect dbo.islower('How many times must i tell you')\r\nSelect dbo.islower('how many times must i tell yoU')\r\n*\/\r\nRETURNS INT\r\nAS BEGIN\r\n RETURN CASE\r\n WHEN PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%',\r\n @string COLLATE Latin1_General_CS_AI) > 0 THEN 0\r\n ELSE 1\r\n END\r\n END\r\nGO\r\n<\/pre>\nIsTitle<\/b> string Function<\/h3>\n
true<\/code> if the string is a titlecased string and there is at least one character, for example uppercase characters may only follow uncased characters and lowercase characters only cased ones. Return false otherwise.<\/p>\nIF OBJECT_ID(N'isTitle') IS NOT NULL\r\n DROP FUNCTION isTitle\r\nGO\r\nCREATE FUNCTION dbo.[isTitle] (@string VARCHAR(MAX)) \r\n\/*\r\nSelect dbo.IsTitle('How Many Times Must I Tell You')\r\nSelect dbo.IsTitle('this function is pretty useless')\r\nSelect dbo.IsTitle(dbo.title('this function is pretty useless'))\r\n*\/\r\nRETURNS INT\r\nAS BEGIN\r\n RETURN CASE\r\n WHEN PATINDEX('%[a-z][ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', @string\r\n COLLATE Latin1_General_CS_AI) > 0 THEN 0\r\n WHEN PATINDEX('%[^A-Za-z][abcdefghijklmnopqrstuvwxyz]%', @string\r\n COLLATE Latin1_General_CS_AI) > 0 THEN 0\r\n ELSE 1\r\n END\r\n END\r\nGO\r\n<\/pre>\nIsSpace<\/b> string Function<\/h3>\n
0<\/code> otherwise.<\/p>\nIF OBJECT_ID(N'IsSpace') IS NOT NULL\r\n DROP FUNCTION IsSpace\r\nGO\r\nCREATE FUNCTION dbo.[IsSpace] (@string VARCHAR(MAX)) \r\n\/*\r\nSelect dbo.IsSpace('how many times must i tell you')\r\nSelect dbo.IsSpace(' <>[]{}\"!@#$%9 )))))))')\r\nSelect dbo.IsSpace(' ????\/>.<,')*\/\r\nRETURNS INT\r\nAS BEGIN\r\n RETURN CASE WHEN PATINDEX(\r\n '%[A-Za-z0-9-]%', @string COLLATE Latin1_General_CS_AI\r\n ) > 0 THEN 0\r\n ELSE 1\r\n END\r\n END\r\nGO\r\n<\/pre>\nLJust<\/b> -Left justify string Function<\/h3>\n
@String<\/code> left justified in a string of length width. Padding is done using the specified fillchar<\/code> string (default is a space). The original string is returned if width is less than len(s)<\/code>.<\/p>\nIF OBJECT_ID(N'LJust') IS NOT NULL\r\n DROP FUNCTION LJust\r\nGO\r\nCREATE FUNCTION LJust\r\n (\r\n @String VARCHAR(MAX),\r\n @width INT,\r\n @fillchar VARCHAR(10) = ' '\r\n )\r\n\/*\r\ne.g.\r\n\r\nselect dbo.LJust('Help me please',5,'*-')\r\nselect dbo.LJust('error',100,'*!=')\r\nselect dbo.LJust('error',null,null)\r\nselect dbo.LJust(null,default,default)\r\n\r\n*\/\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n IF @string IS NULL\r\n RETURN NULL\r\n DECLARE @LenString INT\r\n DECLARE @LenFiller INT\r\n-- Declare the return variable here\r\n SELECT @lenString = LEN(REPLACE(@String, ' ', '|')),\r\n @Fillchar = COALESCE(@Fillchar, ' '),\r\n @LenFiller = LEN(REPLACE(@Fillchar, ' ', '|')),\r\n @width = COALESCE(@Width, LEN(@String) * 2)\r\n IF @Width < @lenString\r\n RETURN @String\r\n RETURN STUFF(LEFT(\r\n REPLICATE(@Fillchar, (@width \/ @LenFiller) + 1),\r\n @width),\r\n 1, @LenString, @String) \r\n END\r\nGO\r\n<\/pre>\nLStrip<\/b>– remove leading characters from a string<\/h3>\n
None<\/code>, the chars argument defaults to removing whitespace. The chars argument is not a prefix; rather, all combinations of its values are stripped:<\/p>\n-- Select dbo.lstrip('www.example.com','cmowz.')\r\nIF OBJECT_ID(N'Lstrip') IS NOT NULL\r\n DROP FUNCTION Lstrip\r\nGO\r\nCREATE FUNCTION Lstrip\r\n (\r\n @String VARCHAR(MAX),\r\n @chars VARCHAR(255) = ' '\r\n )\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n SELECT @Chars = COALESCE(@Chars, ' ')\r\n IF LEN(@Chars) = 0\r\n RETURN LTRIM(@String)\r\n IF @String IS NULL\r\n RETURN @string\r\n WHILE PATINDEX('[' + @chars + ']%', @string) = 1\r\n BEGIN\r\n SELECT @String = RIGHT(@string,\r\n LEN(REPLACE(@string, ' ', '|')) - 1)\r\n END\r\n RETURN @String\r\n END\r\nGO\r\n\r\nSELECT dbo.lstrip('www.example.com', 'cmowz.')\r\nSELECT dbo.lstrip(' www.example.com', ' ')\r\nSELECT dbo.lstrip(NULL, '[]')\r\n<\/pre>\nrfind<\/b>– Find highest index of Substring<\/h3>\n
IF OBJECT_ID(N'rfind') IS NOT NULL\r\n DROP FUNCTION rfind\r\nGO\r\nCREATE FUNCTION rfind\r\n (\r\n @String VARCHAR(MAX),\r\n @Substring VARCHAR(MAX),\r\n @Start INT = NULL,\r\n @End INT = NULL\r\n )\r\nRETURNS INT\r\nAS BEGIN\r\n IF @substring + @string IS NULL\r\n RETURN NULL\r\n IF CHARINDEX(@substring, @string) = 0\r\n RETURN 0\r\n SELECT @Start = COALESCE(@Start, 1),\r\n @end = COALESCE(@end, LEN(REPLACE(@string, ' ', '|')))\r\n IF @end <= @Start\r\n RETURN 0\r\n SELECT @String = SUBSTRING(@String, @start, @end - @Start + 1)\r\n\r\n RETURN @start - 1\r\n + COALESCE(LEN(REPLACE(@string, ' ', '|'))\r\n -CHARINDEX(REVERSE(@substring),\r\n REVERSE(@substring + @string))\r\n - LEN(REPLACE(@substring, ' ', '|')) + 2, 0)\r\n\r\n END\r\nGO\r\nIF OBJECT_ID(N'Rjust') IS NOT NULL\r\n DROP FUNCTION Rjust\r\nGO\r\n<\/pre>\n
RJust<\/b> -Right justify string Function<\/h3>\n
@String <\/code>right justified in a string of length width<\/code>. Padding is done using the specified fillchar<\/code> string (default is a space). The original string is returned if width is less than len(s)<\/code>.<\/p>\nCREATE FUNCTION Rjust\r\n (\r\n @String VARCHAR(MAX),\r\n @width INT,\r\n @fillchar VARCHAR(10) = ' '\r\n )\r\n\/*\r\ne.g.\r\n\r\nselect dbo.Rjust('Help me please',5,'*-')\r\nselect dbo.Rjust('error',100,'*!=')\r\nselect dbo.Rjust('error',null,null)\r\nselect dbo.Rjust(null,default,default)\r\n\r\n*\/\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n IF @string IS NULL\r\n RETURN NULL\r\n DECLARE @LenString INT\r\n DECLARE @LenFiller INT\r\n-- Declare the return variable here\r\n SELECT @lenString = LEN(REPLACE(@String, ' ', '|')),\r\n @Fillchar = COALESCE(@Fillchar, ' '),\r\n @LenFiller = LEN(REPLACE(@Fillchar, ' ', '|')),\r\n @width = COALESCE(@Width, LEN(@String) * 2)\r\n IF @Width < @lenString\r\n RETURN @String\r\n RETURN STUFF(RIGHT(REPLICATE(@Fillchar,\r\n (@width \/ @LenFiller) + 1),\r\n @width),\r\n @width - @LenString + 1,\r\n @LenString,\r\n @String) \r\n END\r\nGO\r\n<\/pre>\nRStrip<\/b>: remove trailing characters from a string<\/h3>\n
-- Select dbo.Rstrip('www.example.com','cmowz.')\r\nIF OBJECT_ID(N'Rstrip') IS NOT NULL\r\n DROP FUNCTION Rstrip\r\nGO\r\nCREATE FUNCTION Rstrip\r\n (\r\n @String VARCHAR(MAX),\r\n @chars VARCHAR(255) = ' '\r\n )\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n DECLARE @RString VARCHAR(MAX)--the string backwards\r\n SELECT @Chars = COALESCE(@Chars, ' '), @rstring = REVERSE(@String)\r\n IF LEN(@Chars) = 0\r\n RETURN RTRIM(@String)\r\n IF @String IS NULL\r\n RETURN @string\r\n WHILE PATINDEX('[' + @chars + ']%', @Rstring) = 1\r\n BEGIN\r\n SELECT @RString = RIGHT(@Rstring,\r\n LEN(REPLACE(@Rstring, ' ', '|')) - 1)\r\n END\r\n RETURN REVERSE(@RString)\r\n END\r\nGO\r\n\r\nSELECT dbo.Rstrip(' spacious ', ' ')\r\nSELECT dbo.Rstrip(' www.example.com 0', ' 0')\r\nSELECT dbo.Rstrip('mississippi', 'ipz')\r\n<\/pre>\nStrip<\/b>: remove trailing or leading characters from a string<\/h3>\n
None<\/code>, the chars argument defaults to removing whitespace. The chars argument is not a prefix or suffix; rather, all combinations of its values are stripped:<\/p>\nIF OBJECT_ID(N'strip') IS NOT NULL\r\n DROP FUNCTION strip\r\nGO\r\nCREATE FUNCTION Strip\r\n (\r\n @String VARCHAR(MAX),\r\n @chars VARCHAR(255) = ' '\r\n )\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n \r\n RETURN dbo.RStrip(dbo.LStrip(@String, @Chars), @chars)\r\n END\r\nGO\r\n<\/pre>\n
SwapCase<\/b> string Function<\/h3>\n
IF OBJECT_ID(N'SwapCase') IS NOT NULL\r\n DROP FUNCTION SwapCase\r\nGO\r\nCREATE FUNCTION dbo.SwapCase (@string VARCHAR(MAX))\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n\r\n DECLARE @ii INT,\r\n @LenString INT,\r\n @ThisChar CHAR(1)\r\n SELECT @ii = 1, @LenString = LEN(@String)\r\n WHILE @ii <= @LenString\r\n BEGIN\r\n SELECT @ThisChar = SUBSTRING(@string, @ii, 1)\r\n IF @ThisChar BETWEEN 'a' AND 'Z' COLLATE Latin1_General_CS_AI \r\n SELECT @String = STUFF(@string,\r\n @ii,\r\n 1,\r\n CHAR(ASCII(@Thischar) ^ 32))\r\n SELECT @ii = @ii + 1\r\n END\r\n RETURN @string\r\n END\r\n\r\nGO\r\nSELECT dbo.swapcase('What a silly function')\r\nSELECT dbo.SwapCase('This is a Hoary Old Programmer trick. It only\r\nworks with the ASCII character set! !\"\u00c2\u00a3$%^&*()_+1234567890-=[]{}')\r\n\/*Gives:\r\ntHIS IS A hOARY oLD pROGRAMMER TRICK. iT ONLY\r\nWORKS WITH THE ascii CHARACTER SET! !\"\u00c2\u00a3$%^&*()_+1234567890-=[]{} *\/\r\n\r\nGO\r\n<\/pre>\nTitle<\/b> string Function<\/h3>\n
@String<\/code>, i.e. words start with uppercase characters, all remaining cased characters are lowercase.<\/p>\nIF OBJECT_ID(N'Title') IS NOT NULL\r\n DROP FUNCTION Title\r\nGO\r\nCREATE FUNCTION [dbo].[title] (@string VARCHAR(MAX))\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n\r\n DECLARE @Next INT\r\n WHILE 1 = 1\r\n BEGIN\r\n --find word space followed by lower case letter\r\n --This makes assumptions about the language\r\n SELECT @next = PATINDEX('%[^a-zA-Z][abcdefghijklmnopqurstuvwxyz]%',\r\n ' ' + @string COLLATE Latin1_General_CS_AI)\r\n IF @next = 0\r\n BREAK\r\n SELECT @String = STUFF(@String,\r\n @Next,\r\n 1,\r\n UPPER(SUBSTRING(@String, @Next, 1)))\r\n END\r\n RETURN @string\r\n END\r\n <\/pre>\nzfill<\/b>: left-fill the numeric string with zeros<\/h3>\n
width<\/code>. The original string is returned if width is less than len(s)<\/code>.<\/p>\nGO\r\nIF OBJECT_ID(N'zfill') IS NOT NULL\r\n DROP FUNCTION zfill\r\nGO\r\nCREATE FUNCTION dbo.zfill\r\n (\r\n @String VARCHAR(MAX),\r\n @Width VARCHAR(255) = ' '\r\n )\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n RETURN dbo.Rjust(@string, @Width, '0')\r\n END\r\nGO\r\nSELECT dbo.zFill('789', 10) \r\n<\/pre>\n