RSS feed for InstantSpot site fro

SQL Server List Length Function

Tue, 19 Feb 2008 16:54:00 GMT

I know lists aren't good to store in a database, but something came up in which I needed to be able to get the list length of a VARCHAR filed. Instead of having to loop it each time, I decided to see what kind of functions were already out there. I found a count string function, but it wasn't exactly what I was looking for, so I took the idea and ran with it.

This function takes a list (VARCHAR) and delimiter (VARCHAR) and returns the number of items in the list.

/*
	UDF_LISTLEN
	==================================================
	Created on 02/19/2008 by Robert Froehling
		- returns the length of a list
		- this was modified from count string function at the link below
		- http://www.sql-server-helper.com/functions/count-string.aspx
*/

CREATE FUNCTION UDF_LISTLEN(
	@list VARCHAR(8000),
	@delim VARCHAR(5))
RETURNS TINYINT
BEGIN
	-- define the vars to use
	DECLARE @modString VARCHAR(8000)
	DECLARE @newString VARCHAR(8000)
	DECLARE @oldStringLen SMALLINT
	DECLARE @modStringLen SMALLINT
	DECLARE @newStringLen SMALLINT
	DECLARE @delimLen TINYINT
	DECLARE @diff TINYINT

	-- set some default vars
	SET @oldStringLen = LEN(@list)
	SET @delimLen = LEN(@delim)
	SET @modString = @list
	SET @modStringLen = LEN(@modString)

	-- if the first part of the string is the delim then remove it
	IF SUBSTRING(@modString, 1, @delimLen) = @delim
	BEGIN
		SET @modString = SUBSTRING(@modString, @delimLen + 1, @oldStringLen - @delimLen)
		SET @modStringLen = LEN(@modString)
	END

	-- if the last part of the string is the delim then remove it
	IF SUBSTRING(@modString, @modStringLen - @delimLen + 1, @delimLen) = @delim
	BEGIN
		SET @modString = SUBSTRING(@modString, 1, @modStringLen - @delimLen)
		SET @modStringLen = LEN(@modString)
	END

	-- count the lengths and setup the diff to return
	SET @newString = REPLACE(@list, @delim, '')
	SET @newStringLen = LEN(@newString)
	SET @diff = @modStringLen - @newStringLen + 1

	RETURN @diff
END

Here is a usage example.

DECLARE @list VARCHAR(500)
DECLARE @delim VARCHAR(1)
SET @list = 'fro,ev,jc,ds'
SET @delim = ','
PRINT master.dbo.udf_listlen(@list, @delim)

Update: I should point out that I've only tested this on SQL Server 2000.

Reminder To Self - Cannot Concatenate NULL in SQL Server 2000

Fri, 08 Feb 2008 22:24:00 GMT

In SQL Server 2000, I'm not sure about 2005 yet, you can't concatenate on a NULL value. The result will continue to be NULL.

By running this you get an output of NULL.

DECLARE @my_string VARCHAR(20)
SET @my_string = @my_string + 'something'
PRINT @my_string

You have to set the variable to something other than NULL before using concatenation.

DECLARE @my_string VARCHAR(20)
SET @my_string = ''
SET @my_string = @my_string + 'something'
PRINT @my_string

This had me hung up for almost an hour.

SQL Server Data Types (ntext, text, and image) To Be Removed

Thu, 07 Feb 2008 19:19:00 GMT

I was doing some research on data types in various DB engines when you came across the SQL Server 2005 documentation on data types and found that the ntext, text, and image data types will be removed in a future version of SQL Server.

When you plan on upgrading to a new version of anything, make sure to check the documentation for any deprecated functionality that you're using.

This doesn't impact me at the moment, as we haven't even upgraded to 2005 yet.

Office Document Behavior Change in Office 2007

Wed, 25 Apr 2007 13:51:56 GMT

Here is an article on how the behavior of opening Office documents from a browser changes in Office 2007. While I think the new behavior is a good thing for most cases, it does effect one of the applications that I work on.

http://blogs.msdn.com/excel/archive/2006/09/26/771221.aspx

Use mail merge to format and print mailing labels

Wed, 25 Apr 2007 13:49:11 GMT

Here is a pretty good article (with demo video) on how to use the mail merge feature in Word 2003 to create labels.

http://office.microsoft.com/en-us/word/HA011903941033.aspx

Friday

Fri, 15 Sep 2006 19:54:35 GMT

Well, it's Friday. Thank goodness for that.