Click here to monitor SSC
  • Av rating:
  • Total votes: 61
  • Total comments: 26
Robyn Page and Phil Factor

SQL String User Function Workbench: part 1

15 April 2008

/* 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 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.
IF OBJECT_ID(N'Capitalize') IS NOT NULL
  
DROP FUNCTION Capitalize
GO
CREATE FUNCTION [dbo].[Capitalize] (@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS BEGIN
      DECLARE
@FirstAsciiChar INT

      SELECT  
@FirstAsciiChar =
              
PATINDEX('%[^a-zA-Z][abcdefghijklmnopqurstuvwxyz]%', ' '
                  
+ @string  COLLATE Latin1_General_CS_AI)
      
IF @FirstAsciiChar > 0
        
SELECT   @String = STUFF(@String,
                                  
@FirstAsciiChar,
                                  
1,
                                  
UPPER(SUBSTRING(@String, @FirstAsciiChar, 1)))
      
RETURN @string
  
END
GO
SELECT   dbo.capitalize('god save her majesty')
--God save her majesty
GO

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

IF OBJECT_ID(N'Center') IS NOT NULL
  
DROP FUNCTION Center
GO
CREATE FUNCTION Center
  
(
    
@String VARCHAR(MAX),
    
@width INT,
    
@fillchar VARCHAR(10) = ''''    )
/*
e.g.

select dbo.center('Help me please',100,'*')
select dbo.center('error',100,'*!=')
select dbo.center('error',null,null)
select dbo.center(null,null,null)

*/
RETURNS VARCHAR(MAX)
AS BEGIN
      IF
@string IS NULL
        
RETURN NULL
      
DECLARE @LenString INT
      DECLARE
@LenResult INT
-- Declare the return variable here
      
SELECT   @lenString = LEN(@String), @Fillchar = COALESCE(@Fillchar, ' '), @width = COALESCE(@Width, LEN(@String) * 2)
      
SELECT   @lenResult = CASE WHEN @LenString > @Width THEN @LenString
                                
ELSE @width
                            
END
      RETURN
STUFF(REPLICATE(@fillchar, @lenResult / LEN(REPLACE(@FillChar, ' ', '|'))), (@LenResult - LEN(@String) + 2) / 2, @lenString, @String)
  
END
GO

GO

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

IF OBJECT_ID(N'Count') IS NOT NULL
  
DROP FUNCTION [Count]
GO
CREATE FUNCTION dbo.[Count]
  
(
    
@string VARCHAR(MAX),
    
@Sub VARCHAR(MAX),
    
@start INT = NULL,
    
@end INT = NULL
   )
RETURNS INT
AS BEGIN
      DECLARE
@more INT
      DECLARE
@count INT
      IF
@string = NULL
        
RETURN NULL
      
SELECT   @count = 0, @more = 1, @Start = COALESCE(@Start, 1), @end = COALESCE(@end, LEN(@string))
      
SELECT   @end = CASE WHEN @end > LEN(@string) THEN LEN(@string)
                          
ELSE @end
                      
END, @Start = CASE WHEN @start > LEN(@string) THEN LEN(@string)
                                        
ELSE @start
                                    
END
      WHILE
@more <> 0
        
BEGIN
            SELECT  
@more = PATINDEX('%' + @sub + '%', SUBSTRING(@string, @Start, @End - @start + 1))
            
IF @more > 0
              
SELECT   @Start = @Start + @more, @count = @count + 1
            
IF @start >= @End
              
SELECT   @more = 0
        
END
      RETURN
@count
  
END
GO

SELECT   dbo.COUNT('The artistic temperament is something that afflicts amateurs', '[^a-z][a-z]', NULL, NULL)
--wordcount (not include first word) 4
SELECT   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)
--37 vowels
SELECT   dbo.COUNT('45667892398', '8', NULL, NULL)
--2
SELECT   dbo.COUNT('if something is worth doing, it is worth doing badly', 'worth doing', 17, 46)
--2

GO

-- =================================================
-- 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.
IF OBJECT_ID(N'EndsWith') IS NOT NULL
  
DROP FUNCTION EndsWith
GO
CREATE FUNCTION EndsWith
  
(
    
@String VARCHAR(MAX),
    
@suffix VARCHAR(MAX),
    
@start INT = NULL,
    
@end INT = NULL
   )
RETURNS INT
AS BEGIN
      SELECT  
@Start = COALESCE(@Start, 1),
              
@End = COALESCE(@End, LEN(@String))
      
IF @string IS NULL OR @suffix IS NULL
        
RETURN NULL
      
SELECT   @end = CASE WHEN @end > LEN(@string)
                                
THEN LEN(@string)
                          
ELSE @end
                      
END,
              
@Start = CASE WHEN @start > LEN(@string)
                                
THEN LEN(@string)
                          
ELSE @start
                      
END

      RETURN
PATINDEX('%' + @suffix,
                      
SUBSTRING(@string,
                      
@Start,
                      
@End - @start + 1))
  
END
GO

SELECT   dbo.endswith('Silence is the unbearable repartee', 'tee',
                                                    
DEFAULT, DEFAULT)
SELECT   dbo.endswith('a yawn is a silent shout', 'shout', 3, DEFAULT)
SELECT   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,
                      
35)
SELECT   dbo.endswith('Prudent dullness marked him out as project manager.', '[.;:,]', DEFAULT, DEFAULT)

-- =================================================
-- Expand Tabs in a string
-- =================================================
-- Returns a copy of @String where all tab characters
-- are expanded using spaces.
IF OBJECT_ID(N'ExpandTabs') IS NOT NULL
  
DROP FUNCTION ExpandTabs
GO
CREATE FUNCTION dbo.[ExpandTabs]
  
(
    
@String VARCHAR(MAX),
    
@tabsize INT = NULL
   )
RETURNS VARCHAR(MAX)
AS BEGIN
      SELECT  
@tabsize = COALESCE(@tabsize, 4)
      
IF @string IS NULL
        
RETURN NULL
      
DECLARE @OriginalString VARCHAR(MAX),
        
@DetabbifiedString VARCHAR(MAX),
        
@Column INT,
        
@Newline INT
      SELECT  
@OriginalString = @String, @DeTabbifiedString = '',
              
@NewLine = 1, @Column = 1
      
WHILE PATINDEX('%[' + CHAR(9) + CHAR(10) + ']%', @OriginalString) > 0
        
BEGIN--do we need to expand tabs?
            
IF CHARINDEX(CHAR(9), @OriginalString + CHAR(9))
                   >
CHARINDEX(CHAR(10), @OriginalString + CHAR(10))
              
BEGIN--we have to deal with a CR
                  
SELECT   @NewLine = 1, @Column = 1,
                          
@DeTabbifiedString = @DeTabbifiedString
                            
+ SUBSTRING(@OriginalString,
                                        
1,
                                        
CHARINDEX(CHAR(10), @OriginalString)),
                          
@OriginalString = STUFF(@OriginalString, 1,
                                                  
CHARINDEX(CHAR(10),
                                                          
@OriginalString),'')
              
END
            ELSE
               BEGIN
--de-tabbifying
                  
SELECT   @Column = @column
                            
+ CHARINDEX(CHAR(9),
                                    
@OriginalString + CHAR(9)) - 1,
                            
@DeTabbifiedString = @DeTabbifiedString
                                
+ SUBSTRING(@OriginalString, 1,
                                            
CHARINDEX(CHAR(9),@OriginalString)
                                              -
1)
                  
SELECT   @DeTabbifiedString = @DeTabbifiedString
                                      
+ SPACE(@TabSize - (@column % @TabSize)),
                          
@OriginalString = STUFF(@OriginalString, 1,
                                                  
CHARINDEX(CHAR(09),
                                                              
@OriginalString),
                                                  
'')
                  
SELECT   @Column = @Column + (@TabSize - (@column % @TabSize))
              
END
         END
      RETURN
@DeTabbifiedString + @Originalstring
  
END
GO

-- =================================================
-- IsAlNum string Function
-- =================================================
-- Returns Non-Zero if all characters in @String are
-- alphanumeric, 0 otherwise.*/

IF OBJECT_ID(N'IsAlnum') IS NOT NULL
  
DROP FUNCTION IsAlnum
GO
CREATE FUNCTION dbo.[IsAlnum] (@string VARCHAR(MAX))  
/*
Select dbo.isalnum('how many times must I tell you')
Select dbo.isalnum('345rtp')
Select dbo.isalnum('co10?')
*/
RETURNS INT
AS BEGIN
      RETURN
CASE WHEN PATINDEX('%[^a-zA-Z0-9]%', @string) > 0 THEN 0
                  
ELSE 1
            
END
   END
GO

-- =================================================
-- IsAlpha string Function
-- =================================================
-- Returns Non-Zero if all characters in @String are
-- alphabetic, 0 otherwise.*/
IF OBJECT_ID(N'IsAlpha') IS NOT NULL
  
DROP FUNCTION IsAlpha
GO
CREATE FUNCTION dbo.IsAlpha (@string VARCHAR(MAX))  
--Select dbo.isalpha('how many times must I tell you')
--Select dbo.isalpha('SQLsequel')
--Select dbo.isalpha('co10')
RETURNS INT
AS BEGIN
      RETURN
CASE WHEN PATINDEX('%[^a-zA-Z]%', @string) > 0 THEN 0
                  
ELSE 1
            
END
   END
GO

-- =================================================
-- IsDigit string Function
-- =================================================
-- Returns Non-Zero if all characters in @string are
--  digit (numeric) characters, 0 otherwise.

IF OBJECT_ID(N'IsDigit') IS NOT NULL
  
DROP FUNCTION IsDigit
GO
CREATE FUNCTION dbo.[IsDigit] (@string VARCHAR(MAX))  
/*
Select dbo.isdigit('how many times must I tell you')
Select dbo.isdigit('294856')
Select dbo.isdigit('569.45')
*/
RETURNS INT
AS BEGIN
      RETURN
CASE WHEN PATINDEX('%[^0-9]%', @string) > 0 THEN 0
                  
ELSE 1
            
END
   END
GO

-- =================================================
-- IsLower string Function
-- =================================================
-- Returns Non-Zero if all characters in s are
-- lowercase characters, 0 otherwise.

IF OBJECT_ID(N'IsLower') IS NOT NULL
  
DROP FUNCTION IsLower
GO
CREATE FUNCTION dbo.[IsLower] (@string VARCHAR(MAX))  
/*
Select dbo.islower('how many times must i tell you')
Select dbo.islower('how many times must I tell you')
Select dbo.islower('How many times must i tell you')
Select dbo.islower('how many times must i tell yoU')
*/
RETURNS INT
AS BEGIN
      RETURN
CASE
          
WHEN PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%',
                    
@string  COLLATE Latin1_General_CS_AI) > 0 THEN 0
                  
ELSE 1
            
END
   END
GO

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

IF OBJECT_ID(N'isTitle') IS NOT NULL
  
DROP FUNCTION isTitle
GO
CREATE FUNCTION dbo.[isTitle] (@string VARCHAR(MAX))  
/*
Select dbo.IsTitle('How Many Times Must I Tell You')
Select dbo.IsTitle('this function is pretty useless')
Select dbo.IsTitle(dbo.title('this function is pretty useless'))
*/
RETURNS INT
AS BEGIN
      RETURN
CASE
          
WHEN PATINDEX('%[a-z][ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', @string
                    COLLATE Latin1_General_CS_AI
) > 0 THEN 0
          
WHEN PATINDEX('%[^A-Za-z][abcdefghijklmnopqrstuvwxyz]%', @string
                    COLLATE Latin1_General_CS_AI
) > 0 THEN 0
                  
ELSE 1
            
END
   END
GO



-- =================================================
-- IsSpace string Function
-- =================================================
-- Returns Non-Zero if all characters in s are
-- whitespace characters, 0 otherwise.

IF OBJECT_ID(N'IsSpace') IS NOT NULL
  
DROP FUNCTION IsSpace
GO
CREATE FUNCTION dbo.[IsSpace] (@string VARCHAR(MAX))  
/*
Select dbo.IsSpace('how many times must i tell you')
Select dbo.IsSpace(' <>[]{}"!@#$%9  )))))))')
Select dbo.IsSpace(' ????/>.<,')*/
RETURNS INT
AS BEGIN
      RETURN
CASE WHEN PATINDEX(
              
'%[A-Za-z0-9-]%', @string  COLLATE Latin1_General_CS_AI
                                
) > 0 THEN 0
                  
ELSE 1
            
END
   END
GO

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

IF OBJECT_ID(N'LJust') IS NOT NULL
  
DROP FUNCTION LJust
GO
CREATE FUNCTION LJust
  
(
    
@String VARCHAR(MAX),
    
@width INT,
    
@fillchar VARCHAR(10) = ' '
  
)
/*
e.g.

select dbo.LJust('Help me please',5,'*-')
select dbo.LJust('error',100,'*!=')
select dbo.LJust('error',null,null)
select dbo.LJust(null,default,default)

*/
RETURNS VARCHAR(MAX)
AS BEGIN
      IF
@string IS NULL
        
RETURN NULL
      
DECLARE @LenString INT
      DECLARE
@LenFiller INT
-- Declare the return variable here
      
SELECT   @lenString = LEN(REPLACE(@String, ' ', '|')),
              
@Fillchar = COALESCE(@Fillchar, ' '),
              
@LenFiller = LEN(REPLACE(@Fillchar, ' ', '|')),
              
@width = COALESCE(@Width, LEN(@String) * 2)
      
IF @Width < @lenString
        
RETURN @String
      
RETURN STUFF(LEFT(
                      
REPLICATE(@Fillchar, (@width / @LenFiller) + 1),
                      
@width),
                    
1, @LenString, @String)  
  
END
GO

-- =================================================
-- 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:
--     Select dbo.lstrip('www.example.com','cmowz.')
IF OBJECT_ID(N'Lstrip') IS NOT NULL
  
DROP FUNCTION Lstrip
GO
CREATE FUNCTION Lstrip
  
(
    
@String VARCHAR(MAX),
    
@chars VARCHAR(255) = ' '
  
)
RETURNS VARCHAR(MAX)
AS BEGIN
      SELECT  
@Chars = COALESCE(@Chars, ' ')
      
IF LEN(@Chars) = 0
        
RETURN LTRIM(@String)
      
IF @String IS NULL
        
RETURN @string
      
WHILE PATINDEX('[' + @chars + ']%', @string) = 1
        
BEGIN
            SELECT  
@String = RIGHT(@string,
                                    
LEN(REPLACE(@string, ' ', '|')) - 1)
        
END
      RETURN
@String
  
END
GO

SELECT   dbo.lstrip('www.example.com', 'cmowz.')
SELECT   dbo.lstrip('        www.example.com', ' ')
SELECT   dbo.lstrip(NULL, '[]')

-- =================================================
-- 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.
IF OBJECT_ID(N'rfind') IS NOT NULL
  
DROP FUNCTION rfind
GO
CREATE FUNCTION rfind
  
(
    
@String VARCHAR(MAX),
    
@Substring VARCHAR(MAX),
    
@Start INT = NULL,
    
@End INT = NULL
   )
RETURNS INT
AS BEGIN
      IF
@substring + @string IS NULL
        
RETURN NULL
      
IF CHARINDEX(@substring, @string) = 0
        
RETURN 0
      
SELECT   @Start = COALESCE(@Start, 1),
              
@end = COALESCE(@end, LEN(REPLACE(@string, ' ', '|')))
      
IF @end <= @Start
        
RETURN 0
      
SELECT   @String = SUBSTRING(@String, @start, @end - @Start + 1)

      
RETURN @start - 1
            
+ COALESCE(LEN(REPLACE(@string, ' ', '|'))
               -
CHARINDEX(REVERSE(@substring),
                        
REVERSE(@substring + @string))
               -
LEN(REPLACE(@substring, ' ', '|')) + 2, 0)

  
END
GO
IF OBJECT_ID(N'Rjust') IS NOT NULL
  
DROP FUNCTION Rjust
GO
 

-- =================================================
-- 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).
CREATE FUNCTION Rjust
  
(
    
@String VARCHAR(MAX),
    
@width INT,
    
@fillchar VARCHAR(10) = ' '
  
)
/*
e.g.

select dbo.Rjust('Help me please',5,'*-')
select dbo.Rjust('error',100,'*!=')
select dbo.Rjust('error',null,null)
select dbo.Rjust(null,default,default)

*/
RETURNS VARCHAR(MAX)
AS BEGIN
      IF
@string IS NULL
        
RETURN NULL
      
DECLARE @LenString INT
      DECLARE
@LenFiller INT
-- Declare the return variable here
      
SELECT   @lenString = LEN(REPLACE(@String, ' ', '|')),
              
@Fillchar = COALESCE(@Fillchar, ' '),
              
@LenFiller = LEN(REPLACE(@Fillchar, ' ', '|')),
              
@width = COALESCE(@Width, LEN(@String) * 2)
      
IF @Width < @lenString
        
RETURN @String
      
RETURN STUFF(RIGHT(REPLICATE(@Fillchar,
                                   (
@width / @LenFiller) + 1),
                                  
@width),
                    
@width - @LenString + 1,
                    
@LenString,
                    
@String)  
  
END
GO


-- =================================================
-- 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:
--     Select dbo.Rstrip('www.example.com','cmowz.')
IF OBJECT_ID(N'Rstrip') IS NOT NULL
  
DROP FUNCTION Rstrip
GO
CREATE FUNCTION Rstrip
  
(
    
@String VARCHAR(MAX),
    
@chars VARCHAR(255) = ' '
  
)
RETURNS VARCHAR(MAX)
AS BEGIN
      DECLARE
@RString VARCHAR(MAX)--the string backwards
      
SELECT   @Chars = COALESCE(@Chars, ' '), @rstring = REVERSE(@String)
      
IF LEN(@Chars) = 0
        
RETURN RTRIM(@String)
      
IF @String IS NULL
        
RETURN @string
      
WHILE PATINDEX('[' + @chars + ']%', @Rstring) = 1
        
BEGIN
            SELECT
@RString = RIGHT(@Rstring,
                                    
LEN(REPLACE(@Rstring, ' ', '|')) - 1)
        
END
      RETURN
REVERSE(@RString)
  
END
GO

SELECT   dbo.Rstrip('   spacious   ', ' ')
SELECT   dbo.Rstrip('        www.example.com     0', ' 0')
SELECT   dbo.Rstrip('mississippi', 'ipz')




-- =================================================
-- 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:
IF OBJECT_ID(N'strip') IS NOT NULL
  
DROP FUNCTION strip
GO
CREATE FUNCTION Strip
  
(
    
@String VARCHAR(MAX),
    
@chars VARCHAR(255) = ' '
  
)
RETURNS VARCHAR(MAX)
AS BEGIN
  
      RETURN
dbo.RStrip(dbo.LStrip(@String, @Chars), @chars)
  
END
GO


-- =================================================
-- SwapCase string Function
-- =================================================
-- Return a copy of the string with uppercase characters
-- converted to lowercase and vice versa.
IF OBJECT_ID(N'SwapCase') IS NOT NULL
  
DROP FUNCTION SwapCase
GO
CREATE FUNCTION dbo.SwapCase (@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS BEGIN

      DECLARE
@ii INT,
        
@LenString INT,
        
@ThisChar CHAR(1)
      
SELECT   @ii = 1, @LenString = LEN(@String)
      
WHILE @ii <= @LenString
        
BEGIN
            SELECT  
@ThisChar = SUBSTRING(@string, @ii, 1)
            
IF @ThisChar BETWEEN 'a' AND 'Z'  COLLATE Latin1_General_CS_AI
                      
SELECT   @String = STUFF(@string,
                                        
@ii,
                                        
1,
                                        
CHAR(ASCII(@Thischar) ^ 32))
            
SELECT   @ii = @ii + 1
        
END
      RETURN
@string
  
END

GO
SELECT   dbo.swapcase('What a silly function')
SELECT   dbo.SwapCase('This is a Hoary Old Programmer trick. It only
works with the ASCII character set! !"£$%^&*()_+1234567890-=[]{}'
)
/*Gives:
tHIS IS A hOARY oLD pROGRAMMER TRICK. iT ONLY
WORKS WITH THE ascii CHARACTER SET! !"£$%^&*()_+1234567890-=[]{} */

GO
-- =================================================
-- Title string Function
-- =================================================
-- Returns a titlecased copy of @String,
-- i.e. words start with uppercase characters, all
-- remaining cased characters are lowercase.

IF OBJECT_ID(N'Title') IS NOT NULL
  
DROP FUNCTION Title
GO
CREATE FUNCTION [dbo].[title] (@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS BEGIN

      DECLARE
@Next INT
      WHILE
1 = 1
        
BEGIN
      
--find word space followed by lower case letter
       --This makes assumptions about the language
            
SELECT   @next = PATINDEX('%[^a-zA-Z][abcdefghijklmnopqurstuvwxyz]%',
                                    
' ' + @string  COLLATE Latin1_General_CS_AI)
            
IF @next = 0
              
BREAK
            SELECT  
@String = STUFF(@String,
                                    
@Next,
                                    
1,
                                    
UPPER(SUBSTRING(@String, @Next, 1)))
        
END
      RETURN
@string
  
END

-- =================================================
-- 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).
GO
IF OBJECT_ID(N'zfill') IS NOT NULL
  
DROP FUNCTION zfill
GO
CREATE FUNCTION dbo.zfill
  
(
    
@String VARCHAR(MAX),
    
@Width VARCHAR(255) = ' '
  
)
RETURNS VARCHAR(MAX)
AS BEGIN
      RETURN
dbo.Rjust(@string, @Width, '0')
  
END
GO
SELECT   dbo.zFill('789', 10)

/* 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

Don't forget that the code can be downloaded from the speech-bubble at the top of the article or from here*/

Robyn Page and Phil Factor

Author profile:


Robyn Page has worked as a consultant with Enformatica and USP Networks with a special interest in the provision of broadcast services over IP intranets. She was also a well known actress, being most famous for her role as Katie Williams, barmaid and man-eater in the Television Series Family Affairs. She is currently having a career break to raise a young family.

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 20 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

Search for other articles by Robyn Page and Phil Factor

Rate this article:   Avg rating: from a total of 61 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Interesting Workbench
Posted by: GSquared (view profile)
Posted on: Wednesday, April 16, 2008 at 8:08 AM
Message: I've implemented a few similar functions, such as Name Case functionality, but nowhere near so many as this.

Subject: Re: Interesting Workbench
Posted by: Phil Factor (view profile)
Posted on: Wednesday, April 16, 2008 at 8:13 AM
Message: Strangely, there are a few of these I don't like, but Robyn drove me onwards through the list. To do Title Case, I rather like my own one...
(The Python one is silly because it uppercases the first letter of Of, The And etc.)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[title] (
@string VARCHAR(8000) )
RETURNS VARCHAR(8000)
AS BEGIN
    DECLARE
@Next INT
    DECLARE
@End INT
    DECLARE
@ii INT
    DECLARE
@first INT
    DECLARE
@word VARCHAR(100)

    
SELECT  @next = PATINDEX('%[a-zA-Z'']%', @string)
    
IF @next=0 RETURN @string
  
SELECT  @String = STUFF(@String, @Next, 1,
                                
UPPER(SUBSTRING(@String, @Next, 1)))
    
    
SELECT @ii=LEN(@string)
    
WHILE @ii>0
      
BEGIN
      
--find word space followed by lower case letter
       --This makes assumptions about the language
        
SELECT @next = PATINDEX('%[^a-zA-Z''][abcdefghijklmnopqurstuvwzyz]%',
                          
RIGHT(@string,@ii)  COLLATE Latin1_General_CS_AI)
        
IF @next = 0
          
BREAK
        SELECT 
@end = PATINDEX('%[a-zA-Z''][^a-zA-Z'']%',
                                
RIGHT(@string,@ii-@next)
                                     +
' '  COLLATE Latin1_General_CS_AI)
        
SELECT @word= SUBSTRING(@string,@Next+LEN(@string)-@ii+1,@end)  
        
IF @Word NOT IN
        
('a','we','do','the','is','all','and','with','some',
                        
'in','from','but','of','to','not')
              
SELECT  @String = STUFF(@String,
                                        
@Next+LEN(@string)-@ii+1,
                                        
1,
                                        
UPPER(SUBSTRING(
                                              
@String,
                                               
@Next+LEN(@string)-@ii+1, 1)
                                              )
                                        )
        
SELECT @ii=@ii-@end                      
      
END
    RETURN
@string
  
END

Subject: _123 TSQL functions
Posted by: Ryan Randall (view profile)
Posted on: Wednesday, April 16, 2008 at 1:14 PM
Message: This is very old but still useful - and since it's related, I thought I'd post it.

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502
"a collection of 123 TSQL functions"

Subject: Re: 123 TSQL functions
Posted by: Phil Factor (view profile)
Posted on: Wednesday, April 16, 2008 at 2:12 PM
Message: You're right. It looks good. At a quick glance, I don't think there are that many duplicates!
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502&lngWId=5

Subject: Capitalise function
Posted by: puzsol (view profile)
Posted on: Wednesday, April 16, 2008 at 6:29 PM
Message: Is there a reason why America seems to like to put a 'z' instead of an 's' in so many words (Capitalize being one of them), rather than sticking to the Queen's English?

Now it seems that you have also decided to replace 'x' with 'z' (see the lower-case alphabet in the PATINDEX line in both the Title and Capitalize functions)

lol (-8

Subject: SwapCase function
Posted by: Josh (view profile)
Posted on: Wednesday, April 16, 2008 at 6:32 PM
Message: The code looks goofy.

Subject: Lstrip function
Posted by: Josh (view profile)
Posted on: Wednesday, April 16, 2008 at 6:50 PM
Message: I just noticed and iif in Lstrip. Even with the bugs, this is my favorite simple-talk column.

Subject: Capitalize function useless
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 16, 2008 at 11:13 PM
Message: It doesn't matter what character the strings starts with, just make it Upper! If it's digit it won't have any effect, so you don't need all that fuss that will kill the performance, simply use SQL internal function to do that.
;o)

Subject: Re: mistake in Capitalize and phantom IIF
Posted by: Robyn Page (view profile)
Posted on: Thursday, April 17, 2008 at 2:30 AM
Message: OOps! I blame Phil! We decided to fix the zxz typo in the article and source file, as we didn't want that typo to propagate!. The IIF was a burp in Phil's prettifier.(the source was OK!) We're not sure what to do about the goofiness of the SwapCase function. Neither of I could think of a real use for it (we thought we ought to put it in just for the sake of completeness), and so we couldn't resist using a bitwise exclusive or in a function just so we could say we'd actually found a purpose for using it in SQL (Phil once managed to create bitmaps in an IMAGE, but he is now fully recovered.)

Subject: Alternative zFill function
Posted by: Andy (view profile)
Posted on: Thursday, April 17, 2008 at 4:48 AM
Message: IF OBJECT_ID(N'zfill') IS NOT NULL
DROP FUNCTION zfill
GO
CREATE FUNCTION dbo.zfill
(
@String VARCHAR(8000),
@Width VARCHAR(255) = ' '
)
RETURNS VARCHAR(8000)
AS BEGIN
RETURN Right(Replicate('0',@Width)+@String,@Width)
END
GO
SELECT dbo.zFill('789', 10)

Subject: Centre
Posted by: Andy (view profile)
Posted on: Thursday, April 17, 2008 at 4:52 AM
Message: That Centre function took me right back to when I was programming on the Zx Spectrum.

Subject: Re: Center
Posted by: Phil Factor (view profile)
Posted on: Thursday, April 17, 2008 at 6:54 AM
Message: Yeah. Me too. Funnily, I had to use one for an email report the other day that was text based. The user wanted the title to be centered. Shame I threw away the speccie. (a cheap Z80-based british computer of the 1980s that was reputed by its creator to be able to control a power station.)
Presumably, now one should nowadays just do
<div style="text-align:center"><h1>MyTitle</h1></div>

Subject: By Goofy I mean..
Posted by: Josh (view profile)
Posted on: Thursday, April 17, 2008 at 2:31 PM
Message: There is an extra "co" in the middle, right after the between.

Subject: Re: Goofy
Posted by: Phil Factor (view profile)
Posted on: Thursday, April 17, 2008 at 3:53 PM
Message: it should read ...
IF @ThisChar BETWEEN 'a' AND 'Z' COLLATE Latin1_General_CS_AI
... It was OK in the script but not in the article. We thought we ought to fix it so it isn't there any more now.

Thanks a lot for spotting that, Josh. As you could imagine, this article was a bit complex to put together! The Prettifier seems to be having the day off!

Subject: Nice overview
Posted by: ALZDBA (view profile)
Posted on: Friday, April 18, 2008 at 6:24 AM
Message: Nice overview ;)

apparently the link for the script download does not work.

Subject: re: nice overview
Posted by: Andrew Clarke (view profile)
Posted on: Friday, April 18, 2008 at 6:50 AM
Message: ..does now! Mea Culpa

Subject: tnx
Posted by: neda (not signed in)
Posted on: Sunday, April 20, 2008 at 10:46 PM
Message: tanks,for your atention.
and tanks because of sending this good article for me.

Subject: Numeric vs string
Posted by: Anny mouse (not signed in)
Posted on: Tuesday, April 22, 2008 at 1:34 AM
Message: What defines alfanumeric ? Is '01' a number or string ?
Is '01' the same as 1 ?
In this case yes.
In this case +1 is not a digit ? Doh.
What about other characters : #@%*[}.....

Subject: Great articale
Posted by: Anonymous (not signed in)
Posted on: Tuesday, April 22, 2008 at 3:27 AM
Message: Found the article really good, nice to see how other people do there capitalize and title case functions.

Thank you.

To puzsol you might what to check the queens english out http://www.askoxford.com/asktheexperts/faq/aboutspelling/ize?view=uk

Subject: New alphabet? (PATINDEX string again)
Posted by: Derek (not signed in)
Posted on: Tuesday, April 22, 2008 at 3:33 AM
Message: Insisting that 'q' is always followed by 'u', even in an alphabetic list of lower case letters is a bit much, although having 2 'u's obviously doesn't affect anything.

Subject: Great work
Posted by: A Fan (not signed in)
Posted on: Tuesday, April 22, 2008 at 8:53 AM
Message: Hello. You are smart and smoking hot. A very rare combination.

Thanks for all of your articles.

Subject: Lost it
Posted by: Anonymous (not signed in)
Posted on: Tuesday, April 22, 2008 at 4:59 PM
Message: I lost it somewhere after god and majesty in the script.

Subject: re: Lost it
Posted by: Robyn Page (view profile)
Posted on: Wednesday, April 23, 2008 at 3:37 AM
Message: I suppose you think Irony is something that an axey is made from.

Subject: Corrections
Posted by: puzsol (view profile)
Posted on: Wednesday, April 30, 2008 at 5:36 PM
Message: Thankyou to the anonymous person who sorted out the ize argument... though from an international point of view I still find it annoying....

Now how about the Aluminum / Aluminium mixup?

Subject: Nice overview, a lot of useful things on one place
Posted by: Matjaz Justin (view profile)
Posted on: Monday, August 25, 2008 at 8:58 AM
Message: Function dbo.Count needs a little modification.

SELECT dbo.COUNT('yyy', 'y', null, null)
-- 2 ??

Subject: Function dbo.Count needs a little more modification.
Posted by: Craig Hathaway (view profile)
Posted on: Sunday, February 22, 2009 at 8:16 PM
Message: SELECT dbo.Count('if something''s worth doing, it is worth doing badly', 'worth doing', 17, 46)
-- 1?

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.