02 October 2009

SQL "list" Functionality

Hey

I recently had to take a FullName field in sql and split it into first and last names. To complicate matters further the fullname had an initial in it and the fields were backwards:

Adams, Douglas B
Fox, Michael J
Spears, Britney M
Rose, Axel

Obviously in cfml this would be easy-peasy using list functions. I'm sure aspx, jsp and php make it fairly trivial too.

SQL However, wasn't built for this, but alas, that was the way it had to be. If it helps anyone the lastname was fairly simple:

left(column_1,charindex(',',column_1)-1) as lastName

Of course firstname become infinitely more complicated as you have to get everything after the comma and THEN strip everything after the space. PLUS not every user has a middle name (eg Axel Rose). Sounds easy..it's not.
Now subqueries make this a little more straightforward but in this case i didn't have that luxury.
If you need to do that try something like:


select fullString, lastname, calculateFirstName(otherName) as firstname from
(
select fullstring, lastname, calculateWhatsLeft(lastname) as otherName from
(
select fullString, calculateLastName() as lastname from tablename
)
)


(excuse the functions in there, it's just shorthand / pseudocode, you actually need to do the left(...charindex(...)) type stuff i just don't wanna retype.

Anyway as i said i couldn't use subqueries so onward with my plight. I tried coalesce but it needs nulls...urg! I ended up with some hideous case statement.
....back to the drawing board.

i stumbled accross this excellent idea by someone i've never met Daryl Banttari:
http://cfprimer.blogspot.com/2009/01/listfirst-listrest-in-sql-server.html

User functions, not normally a big fan of using custom functions in sql, but this was desperation. I modified the functions to take in a delimiter parameter:



CREATE FUNCTION [dbo].[listFirst] (@list nvarchar(4000),@delim nvarchar(2))
RETURNS nvarchar(4000)
AS BEGIN
IF(@delim is null) SET @delim = ','
DECLARE @pos int
DECLARE @ret nvarchar(4000)
SET @pos = charindex(@delim, @list)
IF @pos > 0
SET @ret = left(@list, @pos-1)
ELSE
set @ret = @list
RETURN @ret
END
GO

CREATE FUNCTION [dbo].[listRest] (@list nvarchar(4000),@delim nvarchar(2))
RETURNS nvarchar(4000)
AS BEGIN
IF(@delim is null) SET @delim = ','
DECLARE @pos int
DECLARE @ret nvarchar(4000)
SET @pos = charindex(@delim, @list)
IF @pos > 0
SET @ret = substring(@list, @pos+1, len(@list)-@pos)
ELSE
SET @ret = ''
RETURN @ret
END



and hey presto:


select column_1,
dbo.listFirst(column_1) as lastname,
dbo.[listFirst2](ltrim(dbo.[listRest](column_1)),' ') as firstname
from tableName

No comments: