A common need in SQL is the ability to iterate over a list as if it were an array. In SQL it is not possible to declare arrays, unlike other programming languages such as ColdFusion, ActionScript and Java. Fortunately, there is a way around this problem: use a User-Defined Functions (UDFs) to create a tabular version of the data. Arrays are, after all, essentially tabular data (at their simplest, one dimension level).

A User-Defined Function, is a function provided by the user of a program or environment. In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements.

The Function Code

Below is the complete function definition:

CREATE FUNCTION dbo.udf_ListToTable
(
	@LIST 		NVARCHAR(4000), 
	@DELIMITER 	NVARCHAR(10) = ','
)
RETURNS @ListTable TABLE 
(
	Item NVARCHAR(200)
)
AS
BEGIN
	DECLARE @LenDel 	INT
	DECLARE @Pos 		INT
	DECLARE @Item 		NVARCHAR(200)
 
	--Get the length of the delimiter, use hack to get around LEN(' ') = 0 issue
	SET @LenDel = LEN(@DELIMITER + '|') - 1 
 
	SET @Pos = CHARINDEX(@DELIMITER, @LIST)
	WHILE @Pos > 0
	BEGIN
		--Get the item
		SET @Item = SUBSTRING(@LIST, 1, @Pos-1)
		--Add it to the table (if not empty string) 
		IF LEN(LTRIM(@Item)) > 0
			INSERT @ListTable (Item) VALUES (LTRIM(@Item))
		--Remove the item from the list
		SET @LIST = STUFF(@LIST, 1, @Pos+@LenDel-1, '')
		--Get the position of the next delimiter
		SET @Pos = CHARINDEX(@DELIMITER, @LIST)		
	END
 
	--Add the last item to the table (if not empty string) 
	IF LEN(LTRIM(@LIST)) > 0
		INSERT @ListTable (Item) VALUES (LTRIM(@LIST))
 
	RETURN 
END
GO

The function simply loops over the list passed into the function. Each list item is then inserted into the variable named @ListTable, which is of type TABLE. The @ListTable variable is then returned out of the function and can be handled the same as any other table.

The Function In Use

A simple demonstration is as follows:

INSERT INTO tableName (column1, column2, column3, column4)
SELECT @variable1, @variable2, myTable.item, GETDATE()
FROM dbo.udf_ListToTable(@list,',') AS myTable

In this example, we insert the same information (@variable1, @variable2) for every instance of an item found in myTable.

This is useful, for example, if you want to apply a setting to a group of users. The group of users could be contained in a list that needs to be parsed as a table, whilst the individual setting details are contained in the other variables.

Download the Code

Download the code, rename the file to .sql and run on your database instance. You will then be able to reference the function in your Stored Procedures.

The SQL REPLACE function enables us to look for a certain character phrase in a string and replace it with another character phrase. The updated string is then returned by the function.

The syntax for this string function is the same for SQL Server, Oracle and Microsoft Access. The syntax is as follows:

REPLACE(stringToLookIn, stringToMatch, replacementsString)

The syntax is fairly straight forward, the stringToMatch parameter is the character phrase that we want to replace, the replacementsString is the character phrase that will replace any occurence of the stringToMatch parameter. If the stringToMatch phrase occurs more than once in the string, then all instances of the phrase will be replaced with the replacement string. If no matches were found then the string is returned unaltered.

If we want to match multiple items, we need to nest the REPLACE function:

REPLACE(REPLACE(stringToLookIn, stringToMatch, replacementsString), stringToMatch, replacementsString)

or set the replaced string into a new variable multiple times:

stringReturned = REPLACE(stringToLookIn, stringToMatch, replacementsString)
stringReturned = REPLACE(stringReturned, stringToMatch, replacementsString)

This is far from ideal, especially the more strings there are to be matched. This is where User-Defined Functions (UDFs) can provide the answer.

A User-Defined Function, is a function provided by the user of a program or environment. In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements.

The Function Code

Below is the complete function definition:

CREATE FUNCTION dbo.udf_ReplaceChars
(
@ReplaceList		VARCHAR(50),
@String			VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
	DECLARE	@CHAR		CHAR(1),
		@Loop		INT
 
	SET @Loop  = 0
	WHILE @Loop < = LEN(@ReplaceList)
	BEGIN
		SET	@Loop = @Loop + 1
		SET	@CHAR = SUBSTRING(@ReplaceList, @Loop, 1)
		SET	@String = REPLACE(@String, @CHAR, '')
	END
 
	RETURN		@String
 
END
GO

The function simply loops over the replace list, finding each instance of the list item in the string in which we want to replace items. The new string is then returned out of the function.

The Function In Use

A very simple use of the replace function could be as follows:

SELECT dbo.udf_ReplaceChars('=,/,<,>,@,~,#', columnName) AS newColumn, columnName
FROM tableName

The function is not restricted to SELECT statements. Below is an example of an UPDATE statement utilising a variable:

UPDATE tableName
SET columnName = dbo.udf_ReplaceChars('=,/,<,>,@,~,#', @variableName)
WHERE idName = @myId

Download the Code

Download the code, rename the file to .sql and run on your database instance. You will then be able to reference the function in your Stored Procedures.

Following on from the RNIB’s web accessibility initiatives, web compliance experts Magus Ltd and The British Standards Institute are working together to create a new publically accessible standard (PAS 124) for websites. Web standards govern the effectiveness, function and appearance of a website, and include: brand, legal, accessibility, search engine optimisation (SEO), usability and technical standards.

Websites are increasingly the key communication vehicle for a company, its brand and products. Despite this, research from Magus shows that many of the world’s leading organisations don’t have formal brand and technical standards defined to govern their websites. Even those that do are failing to effectively implement or enforce them, achieving full compliance with less than 20% of their own web standards. The websites of these organisations significantly under-perform or damage the brand as a result.

PAS 124 will help to protect the significant investment organisations are making in their web presence and online brands, by establishing best practice for “defining, implementing and managing organisational web standards”. It will provide a clear framework to help organisations apply standards effectively to significantly improve online performance and protect the integrity of their brands.

More detail can be found on the BSI’s press release.