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.

SQL Server Identity

Mon, 07 Jan 2008 22:34:08 GMT

Here is a pretty good article about the differences between @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT('tablename') for SQL Server. I didn't even know about IDENT_CURRENT('tablename') before.

http://www.mssqltips.com/tip.asp?tip=1385

Friday

Fri, 15 Sep 2006 19:54:35 GMT

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