Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

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

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('','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('', 'cmowz.')
SELECT dbo.lstrip(' ', ' ')
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('','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(' 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*/

/*

The Return of the Killer Tuples

This workbench finishes of what has been a three-part series of string functions. In it, we introduce the idea of using XML to provide a very simple array for doing string handling. This allows us to use functions for searching and splitting strings that will be familiar to users of procedural languages such as PHP and Python.

The first part, TSQL String Array Workbench showed how the basics worked and then demonstrated how it could be used with a PHP-style string function. What inspired us to write this workshop was when Phil had to endure a PHP programmer sounding off about how much better PHPs string handling as than TSQL. He then made the discovery that it was actually possible to pass a string in an XML parameter, detect the fact and convert it to a single-item list, so as to emulate the facility of PHP and Python to pass either lists or single strings.

Of course, this principle could be extended to arrays and matrices. We don't handle the representation of lists, arrays and matrices in any standard way, as this would be a distraction at this stage, and multi-dimensional lists of arrays aren't used much for strings.

We then got rather diverted by the Python string functions (now string methods) and so wrote the SQL String User Function Workbench: part 1 that emulated all the python string functions that didn't have lists as parameters so we didn't use the XML array mechanism. Unfortunately there were a whole group that did, so here, to round things up are the....

.....SQL String User Functions (from python) that can use lists

Contents

*/ -- =================================================
-- Split Function
-- =================================================
-- Return an array of the words in the string, using
-- @delimiter as a delimiter. If @maxsplit is given, at
-- most @maxsplit splits are done. (thus, the list will
-- have at most maxsplit+1 elements). If @maxsplit is
-- not specified, then there is no limit on the number
-- of splits (all possible splits are made). Consecutive
-- delimiters are not grouped together and are deemed to
-- delimit empty strings. The sep argument may consist
-- of several characters

-- If @Delimiter is not specified or is None, a different
-- splitting algorithm is applied. First, whitespace
-- characters spaces, tabs, newlines, returns, and formfeeds)
-- are stripped from both ends. Then, words are separated by
-- arbitrary length strings of whitespace characters.
-- Consecutive whitespace delimiters are treated as a
-- single delimiter. Splitting an empty string or a string
-- consisting of just whitespace returns an empty list.

-- p.s. we took this second 'splitting algorithm' to mean
-- that a list of the words was required. Our solution is only
-- tested for English and will need fine tuning for other languages
-- Phil swore ages ago that Hell would freeze over before he ever
-- published yet another string-splitting algorithm. We may have
-- hit on a solution to global warming here.

IF OBJECT_ID(N'split') IS NOT NULL
DROP FUNCTION split
GO
CREATE FUNCTION [dbo].[split]
(
@String VARCHAR(8000),
@Delimiter VARCHAR(255) = NULL,
@MaxSplit INT = NULL

)
RETURNS XML
AS BEGIN
DECLARE @results TABLE
(
seqno INT IDENTITY(1, 1),
Item VARCHAR(MAX)
)
DECLARE @xml XML,
@HowManyDone INT, --index of current search
@HowMuchToDo INT,--How much more of the string to do
@StartOfSplit INT,
@EndOfSplit INT,
@SplitStartCharacters VARCHAR(255),
@SplitEndCharacters VARCHAR(255),
@ItemCharacters VARCHAR(255),
@ii INT

SELECT @HowMuchToDo = LEN(@string), @HowManyDone = 0,
@StartOfSplit = 100, @SplitEndCharacters = '[a-z]',
@SplitStartCharacters = COALESCE(@Delimiter,
'[^-a-z'']'),
@EndOfSplit = LEN(@SplitStartCharacters), @ii = 1

WHILE @StartOfSplit > 0--we have a delimiter left to do
AND @HowMuchToDo > 0--there is more of the string to split
AND @ii <= COALESCE(@MaxSplit, @ii)
BEGIN --find the delimiter or the start of the non-word block
SELECT @StartOfSplit = PATINDEX('%' + @SplitStartCharacters + '%',
RIGHT(@String,@HowMuchToDo) COLLATE Latin1_General_CI_AI)

IF @StartOfSplit > 0--if there is a non-word block
AND @delimiter IS NULL
SELECT @EndOfSplit = --find the next word
PATINDEX('%' + @SplitEndCharacters + '%',
RIGHT(@string,@HowMuchToDo- @startOfSplit)
COLLATE Latin1_General_CI_AI)

IF @StartOfSplit > 0--if there is a non-word block or delimiter
AND @ii < COALESCE(@MaxSplit, @ii + 1) --and there is a field
--still to do
INSERT INTO @Results (item)
SELECT LEFT(RIGHT(@String, @HowMuchToDo),
@startofsplit - 1)
ELSE --if not then save the rest of the string
INSERT INTO @Results (item)
SELECT RIGHT(@String, @HowMuchToDo)

SELECT @HowMuchToDo = @HowMuchToDo - @StartOfSplit
- @endofSplit + 1, @ii = @ii + 1
END

--now we simply output the temporary table variable as XML
-- using our standard string-array format
SELECT @xml = (SELECT seqno, item
FROM @results
FOR
XML PATH('element'),
TYPE,
ELEMENTS,
ROOT('stringarray')
)
RETURN @xml
END

GO

IF OBJECT_ID(N'ArrayToTable') IS NULL
RAISERROR ('In order to try these examples, you will need the Array,
ArrayToTable, str_Replace, and Item functions from the TSQL String
Array Workbench',16,1)

--so now we test it out (The real test rig is longer and more boring)
SELECT * FROM dbo.ArrayToTable(dbo.split('If I wanted that c**p from you,
I''d squeeze your head', NULL, NULL))
SELECT dbo.split('How come you always program when drunk?
Because I learned how to when drunk', '?', NULL)
SELECT dbo.split('This is the worst disaster to happen here since I arrived'
,NULL, NULL)
SELECT * FROM dbo.ArrayToTable(dbo.split('When I read about
Service Broker, I find I have
amnesia and Deja vu at the same time
I keep thinking I''ve forgotten
it before', '
', NULL))

/*
-- =================================================
-- SplitLines string Function
-- =================================================
Return a list of the lines in the string,
breaking at line boundaries. Line breaks are not included
in the resulting list unless keepends is given and true.

p.s. This is such a simple modification to 'Split' that you
wonder why they bothered.
*/
IF OBJECT_ID(N'SplitLines') IS NOT NULL
DROP FUNCTION SplitLines
GO
CREATE FUNCTION dbo.SplitLines
(
@String VARCHAR(8000),
@keepends INT=0
)
RETURNS XML
AS BEGIN
DECLARE @Delimiter VARCHAR(5)
SELECT @Delimiter=CASE WHEN COALESCE(@keepends,0)<>0
THEN CHAR(13) ELSE '
' END
RETURN dbo.split(@string, @delimiter, NULL)
END
GO

SELECT * FROM ArrayToTable(dbo.SplitLines('
When the guy who
made the first drawing board
got it wrong, what did
he go back to?
',1))

SELECT * FROM ArrayToTable(dbo.SplitLines('What is another
word for ''Thesaurus''
',0))
-- seqno item
-- ----------- ---------------------
-- 1 What is another
-- 2 word for 'Thesaurus'

-- =================================================
-- within string Function
-- =================================================
-- Return non-zero if the string contains the specified
-- substring, otherwise return False. suffix can also be
-- a list of substrings to look for. With the optional start
-- parameter, the test should begin at that position.
-- With the optional end,the test should stop comparing at
-- that position.

-- ps This isn't a Python method, but it underpins the
-- Startswith, and EndsWith routines.
-- we add the 'contains' function to hide the wildcard.

IF OBJECT_ID(N'within') IS NOT NULL
DROP FUNCTION within
GO
CREATE FUNCTION within
(
@String VARCHAR(MAX),
@Substring XML,
@start INT = NULL,
@end INT = NULL,
@prefixWildcard VARCHAR(1)='%',
@SuffixWildcard VARCHAR(1)='%'
)
RETURNS INT
AS BEGIN
DECLARE @Match INT
SELECT @Start = COALESCE(@Start, 1),
@End = COALESCE(@End, LEN(@String))
IF @string IS NULL OR @Substring IS NULL
RETURN NULL
--convert a single Substring into an array of one.
IF CHARINDEX('', CONVERT(VARCHAR(MAX), @Substring)) = 0
SELECT @Substring = '1'
+ CONVERT(VARCHAR(MAX),@Substring)
+ ''
-- provide sensible defaults for the limiters
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
--and it is one simple SELECT statement!
SELECT @match= COUNT(*) FROM
( SELECT x.y.value('item[1]', 'VARCHAR(200)') AS [Substring ]
FROM @Substring .nodes('//stringarray/element') AS x ( y )
) theSubstrings
WHERE PATINDEX(@SuffixWildcard+SUBSTRING +@prefixWildcard,
SUBSTRING(@string, @Start, @End - @start + 1))>0
RETURN @match
END
GO

SELECT dbo.within('I''m writing an unauthorised autobiography, but
what I''ve always wanted to do is to write a book ending in the word
''mayonnaise''',
dbo.array('mayonnaise,thrifty,art,lust',','),
DEFAULT, DEFAULT, DEFAULT, DEFAULT)
-- 1

-- =================================================
-- EndsWith string Function
-- =================================================
-- Return non-zero if the string ends with the suffix,
-- otherwise return False. The suffix can also be a list of
-- suffixes to look for. With optional start, test string
-- beginning at that position. With optional end, stop
-- comparing string at that position.
IF OBJECT_ID(N'EndsWith') IS NOT NULL
DROP FUNCTION EndsWith
GO
CREATE FUNCTION dbo.EndsWith
(
@String VARCHAR(MAX),
@prefix XML,
@start INT = NULL,
@end INT = NULL
)
RETURNS INT
AS BEGIN
RETURN dbo.within(@String,@prefix,@start,@end,'','%')
END
GO

SELECT dbo.endswith('The IRA are indiscriminately killing men
women and children, and now they''ve killed two Australians
Quote from Margaret Thatcher',
dbo.array('wilson,Reagan,Clinton,Thatcher',','),
DEFAULT, DEFAULT)
SELECT dbo.endswith(
'If we don''t succeed, then we run the risk of failure
Quote from Dan Quayle', 'Quayle', DEFAULT, DEFAULT)

SELECT dbo.endswith(
'Prudent dullness marked him out as project manager.', '[.;:,]',
DEFAULT, DEFAULT)

-- =================================================
-- StartsWith string Function
-- =================================================
-- Return non-zero if the string starts with the prefix,
-- otherwise return False. prefix can also be a list of
-- prefixes to look for. With optional start, test string
-- beginning at that position. With optional end, stop
-- comparing string at that position.
IF OBJECT_ID(N'StartsWith') IS NOT NULL
DROP FUNCTION StartsWith
GO
CREATE FUNCTION dbo.StartsWith
(
@String VARCHAR(MAX),
@prefix XML,
@start INT = NULL,
@end INT = NULL
)
RETURNS INT
AS BEGIN
RETURN dbo.within(@String,@prefix,@start,@end,'%','')
END
GO

SELECT dbo.StartsWith(
'Aside from its purchasing power, money is pretty useless',
dbo.array('power,money,love',','),27,DEFAULT)
-- 1

-- =================================================
-- Contains string Function
-- =================================================
-- Return non-zero if the string contains the substring,
-- otherwise returns 0. substring can also be a list of
-- substrings to look for. With optional start, test string
-- beginning at that position. With optional end, stop
-- comparing string at that position.
IF OBJECT_ID(N'Contains') IS NOT NULL
DROP FUNCTION [Contains]
GO
CREATE FUNCTION dbo.[Contains]
(
@String VARCHAR(MAX),
@substring XML,
@start INT = NULL,
@end INT = NULL
)
RETURNS INT
AS BEGIN
RETURN dbo.within(@String,@substring,@start,@end,'%','%')
END
GO
SELECT dbo.[contains]('What about coming to work for my company?
Will that many people fit under a rock?','work',DEFAULT, DEFAULT)
--1
-- =================================================
-- Join string Function
-- =================================================
-- Joins together the given array AS a string WITH
-- the @separator as separator:
IF OBJECT_ID(N'Join') IS NOT NULL
DROP FUNCTION [Join]
GO
CREATE FUNCTION dbo.[Join]
(
@array XML,
@separator VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS BEGIN
DECLARE @joined VARCHAR(MAX)
--it is conceivable that someone might use a string here, to
--make sure it is XML in our format
IF CHARINDEX('', CONVERT(VARCHAR(MAX), @array)) = 0
SELECT @array = '1'
+ CONVERT(VARCHAR(MAX), @array)
+ ''
--and now once again it is a simple select statement
SELECT @joined=COALESCE(@joined+@separator,'') + item FROM
( SELECT x.y.value('item[1]', 'VARCHAR(200)') AS [item],
x.y.value('seqno[1]', 'INT') AS seqno
FROM @array.nodes('//stringarray/element') AS x ( y )
) f
ORDER BY f.seqno
RETURN @joined
END
GO
SELECT dbo.[join](dbo.array ('Waterp,Repr,Dispr,Al,L,R,Pr,',','),'oof,')
-- Waterpoof,Reproof,Disproof,Aloof,Loof,Roof,Proof,
SELECT dbo.[join](dbo.array ('F,r,i,e,d, ,E,g,g,s',','),'')
-- Fried Eggs

-- =================================================
-- Parts string Function
-- =================================================

-- Split the string at the first occurrence of sep, and RETURN
-- an array containing the part before the separator, the
-- separator itself, and the part after the separator. IF
-- the separator is not found, return an array containing
-- the string itself, followed by two empty strings.

-- p.s. this is not part of the Python suite. It is used
-- to support Partition and RPartition
-- Again, Phil required calming down before he knuckled down
-- to write this, since he once swore he would never publish another
-- string splitting routine

IF OBJECT_ID(N'Parts') IS NOT NULL
DROP FUNCTION Parts
GO
CREATE FUNCTION dbo.Parts
(
@String VARCHAR(MAX),
@sep VARCHAR(MAX),
@Last INT=0
)
RETURNS XML
AS BEGIN
DECLARE @SepPos INT,
@XML AS XML

DECLARE @results TABLE
(
seqno INT IDENTITY(1, 1),
-- the sequence is meaningful here
Item VARCHAR(MAX)
)
IF @last<>0
SELECT @SepPos=dbo.rfind(@string,@sep,DEFAULT,DEFAULT)
ELSE
SELECT @SepPos=CHARINDEX(@Sep,@string)

IF @SepPos>0
INSERT INTO @results(Item)
SELECT LEFT(@String,@SepPos-1)
UNION ALL SELECT @Sep
UNION ALL SELECT RIGHT(@String,LEN(@String)-@Seppos-LEN(@sep)+1)
ELSE
INSERT INTO @results(Item)
SELECT @String
UNION ALL SELECT ''
UNION ALL SELECT ''
SELECT @xml = (SELECT seqno, item
FROM @results
FOR
XML PATH('element'),
TYPE,
ELEMENTS,
ROOT('stringarray')
)
RETURN @xml
END
GO

SELECT * FROM dbo.ArrayToTable(dbo.parts('IS your manager a bookworm?
NO just an ordinary one','?',0))
SELECT dbo.parts('None of my team ever made a fool of me.
well who was it then?','fool',0)

-- =================================================
-- Partition string Function
-- =================================================

-- Split the string at the first occurrence of sep, and RETURN
-- an array containing the part before the separator, the
-- separator itself, and the part after the separator. IF
-- the separator is not found, return an array containing
-- the string itself, followed by two empty strings.

IF OBJECT_ID(N'Partition') IS NOT NULL
DROP FUNCTION Partition
GO
CREATE FUNCTION dbo.Partition
(
@String VARCHAR(MAX),
@Sep VARCHAR(MAX)
)
RETURNS XML
AS BEGIN
RETURN dbo.parts(@String,@sep,0)
END
GO

-- =================================================
-- RPartition string Function
-- =================================================

-- Split the string at the last occurrence of sep, and RETURN
-- an array containing the part before the separator, the
-- separator itself, and the part after the separator. IF
-- the separator is not found, return an array containing
-- the string itself, followed by two empty strings.

IF OBJECT_ID(N'RPartition') IS NOT NULL
DROP FUNCTION RPartition
GO
CREATE FUNCTION dbo.RPartition
(
@String VARCHAR(MAX),
@Sep VARCHAR(MAX)
)
RETURNS XML
AS BEGIN
RETURN dbo.parts(@String,@sep,1)
END
GO
SELECT item FROM dbo.ArrayToTable(dbo.partition(
'Does the name ''Pavlov'' ring a bell?',
'pavlov'))
-- Does the name '
-- pavlov
-- ' ring a bell?

SELECT item FROM dbo.ArrayToTable(dbo.Partition(
'anyone who isn''t pulling his weight is probably pushing his luck','his'))
-- anyone who isn't pulling
-- his
-- weight is probably pushing his luck

SELECT item FROM dbo.ArrayToTable(dbo.RPartition(
'anyone who isn''t pulling his weight is probably pushing his luck','his'))
-- anyone who isn't pulling his weight is probably pushing
-- his
-- luck

-- So, just to summarise, we can create a string array as a variable
DECLARE @DaysOfTheWeek XML
-- we can take a delimites string list, and turn it into an array
SELECT @DaysOfTheWeek=dbo.array(
'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday',',')
-- we can extract a list element
SELECT dbo.item(@DaysOfTheWeek,4)
-- Thursday
--we can find how many there are in the list
SELECT CONVERT(VARCHAR,@DaysOfTheWeek.query('count(/stringarray/element/.)'))

--we can use it as a table...
SELECT * FROM dbo.ArrayToTable(@DaysOfTheWeek)
--
-- seqno

ITPub博客

SQL String User Function Workbench

最新文章