Simple Talk is now part of the Redgate Community hub - find out why

SQL String User Function Workbench: part 1

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.

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!

Contents

  1. Capitalize
  2. Center
  3. Count Substring in String
  4. EndsWith
  5. ExpandTabs
  6. IsAlnum
  7. IsAlpha
  8. IsDigit
  9. IsLower
  10. IsTitle
  11. IsSpace
  12. LJust
  13. LStrip
  14. RFind
  15. RJust
  16. RStrip
  17. Strip
  18. SwapCase
  19. Title
  20. Zfill

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 – there seems little there in the same league as what’s available in Python.

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 STUFF function is intuitive! (Robyn documented the basic string functions in her Robyn Page’s SQL Server String Manipulation Workbench.)

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.

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.

The Python ones we use are…

Capitalize string Function

Return a copy of the string with only its first character capitalized.

Centre string Function

Returns a copy of @String centered in a string of length @width, surrounded by the appropriate number of @fillChar characters

Count substring in string Function

Returns the number of occurrences of substring sub in string s. Allows you to specifying the start and end position of the search.

EndsWith string Function

Return non-zero if the string ends with the specified suffix, otherwise return False. 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.

Expand Tabs in a string

Returns a copy of @String where all tab characters are expanded using spaces.

IsAlNum string Function

Returns Non-Zero if all characters in @String are alphanumeric, 0 otherwise.

IsAlpha string Function

Returns non-zero if all characters in @String are alphabetic, 0 otherwise.

IsDigit string Function

Returns non-zero if all characters in @string are digit (numeric) characters, 0 otherwise.

IsLower string Function

Returns non-zero if all characters in s are lowercase characters, 0 otherwise.

IsTitle string Function

Return true 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.

IsSpace string Function

Returns non-zero if all characters in s are whitespace characters, 0 otherwise.

LJust -Left justify string Function

Returns a copy of @String left justified in a string of length width. Padding is done using the specified fillchar string (default is a space). The original string is returned if width is less than len(s).

LStrip– remove leading characters from a string

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 None, the chars argument defaults to removing whitespace. The chars argument is not a prefix; rather, all combinations of its values are stripped:

rfind– Find highest index of Substring

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.

RJust -Right justify string Function

Returns a copy of @String right justified in a string of length width. Padding is done using the specified fillchar string (default is a space). The original string is returned if width is less than len(s).

RStrip: remove trailing characters from a string

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:

Strip: remove trailing or leading characters from a string

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 None, the chars argument defaults to removing whitespace. The chars argument is not a prefix or suffix; rather, all combinations of its values are stripped:

SwapCase string Function

Return a copy of the string with uppercase characters converted to lowercase and vice versa.

Title string Function

Returns a titlecased copy of @String, i.e. words start with uppercase characters, all remaining cased characters are lowercase.

zfill: left-fill the numeric string with zeros

Return the numeric string left-filled with zeros in a string of length width. The original string is returned if width is less than len(s).

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 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.

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue