The built-in string handling functions in t-sql are all well and good, but there really aren’t any graceful ways of handling lists. As any CF developer that’s been around a while knows, lists are pretty commonplace in practice, and there are times when it makes more sense (or you’re required) to do list handling at the db level rather than at the front end.
The following six SQL Server udf’s provide equivalent functionality to the ColdFusion list functions listfirst, listgetat, listsetat, listlast, listfind, and listcontains. The only thing that differs from their CF counterparts (and a minor one at that) is that they require you to pass a single delimiter, not a set of possible delimiters as in CF.
UPDATE 2/10/2011: now includes a listfind function as well.
UPDATE 10/16/2011: now includes a listsetat function courtesy of Bud Schneegan.
UPDATE 5/6/2012: now includes a listcontains function by request.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 |
CREATE FUNCTION [dbo].[uf_listfirst] ( @list varchar(2000), @delimiter char(1) ) RETURNS varchar(500) /* Purpose: returns the first item in @list based on the character defined in @delimiter Author: Kevin J. Miller (www.websolete.com) Example: SELECT myColumn, dbo.uf_listfirst(myColumn,'/') FROM myTable would return: mykingdom.com/subdir/hello/index.htm mykingdom.com */ AS BEGIN DECLARE @listitem varchar(500), @cpos smallint; SET @listitem = NULL; SET @list = LTRIM(RTRIM(@list))+ @delimiter; SET @cpos = CHARINDEX(@delimiter,@list, 1); IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length BEGIN SET @listitem = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) RETURN @listitem END RETURN @listitem END GO -- ----------------------------------------------------------------------------------------- CREATE FUNCTION [dbo].[uf_listgetat] ( @list varchar(2000), @pos smallint, @delimiter char(1) ) RETURNS varchar(500) /* Purpose: returns the item at position @pos in @list based on the character defined in @delimiter Notes: list positions are 1-based, not 0-based Author: Kevin J. Miller (www.websolete.com) Example: SELECT myColumn, dbo.uf_listgetat(myColumn,3,'/') FROM myTable would return: mykingdom.com/subdir/hello/index.htm hello */ AS BEGIN DECLARE @listitem varchar(500), @cpos smallint, @loop smallint; SET @listitem = NULL; SET @list = LTRIM(RTRIM(@list))+ @delimiter; SET @cpos = CHARINDEX(@delimiter,@list, 1); SET @loop = 0; IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length BEGIN WHILE @cpos > 0 BEGIN SET @listitem = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) SET @loop = @loop + 1 IF @loop = @pos RETURN @listitem SET @list = RIGHT(@list, LEN(@list) - @cpos) SET @cpos = CHARINDEX(@delimiter, @list, 1) END END SET @listitem = NULL -- if we got here, the position specifed exceeds the list length RETURN @listitem END GO -- ---------------------------------------------------------------------------------------- CREATE FUNCTION [dbo].[uf_listsetat] ( @list varchar(8000), @pos smallint, @newelement varchar(2000), @delimiter char(1) ) RETURNS varchar(8000) /* Purpose: sets the item at position @pos in @list based on the character defined in @delimiter Notes: list positions are 1-based, not 0-based Author: Bud Schneehagen - based upon functions originally from Kevin J. Miller's (www.websolete.com) Example: SELECT myColumn, dbo.uf_ListSetAt(myColumn,3,'bye','/') FROM myTable would return: mykingdom.com/subdir/hello/index.htm mykingdom.com/subdir/bye/index.htm */ AS BEGIN DECLARE @newlist varchar(8000), @cpos smallint, @loop smallint; SET @newlist = NULL; SET @list = LTRIM(RTRIM(@list))+ @delimiter; SET @cpos = CHARINDEX(@delimiter,@list, 1); SET @loop = 0; IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length BEGIN WHILE @cpos > 0 BEGIN SET @loop = @loop + 1 IF @loop = 1 BEGIN IF @loop = @pos SET @newlist = @newelement ELSE SET @newlist = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) END ELSE BEGIN IF @loop = @pos SET @newlist = @newlist + @delimiter + @newelement ELSE SET @newlist = @newlist + @delimiter + LTRIM(RTRIM(LEFT(@list, @cpos - 1))) END SET @list = RIGHT(@list, LEN(@list) - @cpos) SET @cpos = CHARINDEX(@delimiter, @list, 1) END END RETURN @newlist END GO -- ---------------------------------------------------------------------------------------- CREATE FUNCTION [dbo].[uf_listlast] ( @list varchar(2000), @delimiter char(1) ) RETURNS varchar(500) /* Purpose: returns the last item in @list based on the character defined in @delimiter Author: Kevin J. Miller (www.websolete.com) Example: SELECT myColumn, dbo.uf_listlast(myColumn,'/') FROM myTable would return: mykingdom.com/subdir/hello/index.htm index.htm */ AS BEGIN DECLARE @listitem varchar(500), @cpos smallint, @loop smallint; SET @listitem = NULL; SET @list = LTRIM(RTRIM(@list))+ @delimiter; SET @cpos = CHARINDEX(@delimiter,@list, 1); SET @loop = 0; IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length BEGIN WHILE @cpos > 0 BEGIN SET @listitem = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) SET @loop = @loop + 1 SET @list = RIGHT(@list, LEN(@list) - @cpos) SET @cpos = CHARINDEX(@delimiter, @list, 1) END END RETURN @listitem END GO -- ---------------------------------------------------------------------------------------- CREATE FUNCTION [dbo].[uf_listfind] ( @list varchar(2000), @item varchar(500), @delimiter char(1) ) RETURNS smallint /* Purpose: returns the position of @item in @list based on the character defined in @delimiter Notes: list positions are 1-based, not 0-based; case-sensitivity will be based on the server collation (most likely will be case insensitive on most default installs) Author: Kevin J. Miller (www.websolete.com) Example: SELECT myColumn, dbo.uf_listfind(myColumn,'admin','/') FROM myTable would return: mykingdom.com/admin/hello/index.htm 2 */ AS BEGIN DECLARE @listitem varchar(500), @cpos smallint, @loop smallint; SET @listitem = NULL; SET @list = LTRIM(RTRIM(@list))+ @delimiter; SET @cpos = CHARINDEX(@delimiter,@list, 1); SET @loop = 0; IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length BEGIN WHILE @cpos > 0 BEGIN SET @listitem = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) SET @loop = @loop + 1 IF @listitem = @item RETURN @loop SET @list = RIGHT(@list, LEN(@list) - @cpos) SET @cpos = CHARINDEX(@delimiter, @list, 1) END END -- if we got here, the item was not found in the list RETURN 0 END GO -- ---------------------------------------------------------------------------------------- CREATE FUNCTION [dbo].[uf_listcontains] ( @list varchar(2000), @substring varchar(500), @delimiter char(1) ) RETURNS smallint /* Purpose: returns the first list position of list item containing @substring in @list based on the character defined in @delimiter Notes: list positions are 1-based, not 0-based; case-sensitivity will be based on the server collation (most likely will be case insensitive on most default installs) Author: Kevin J. Miller (www.websolete.com) Example: SELECT myColumn, dbo.uf_listcontains(myColumn,'ell','/') FROM myTable would return: mykingdom.com/admin/hello/index.htm 3 */ AS BEGIN DECLARE @listitem varchar(500), @cpos smallint, @loop smallint; SET @listitem = NULL; SET @list = LTRIM(RTRIM(@list))+ @delimiter; SET @cpos = CHARINDEX(@delimiter,@list, 1); SET @loop = 0; IF LEN(REPLACE(@list,@delimiter, '')) > 0 -- we have a list of some length BEGIN WHILE @cpos > 0 BEGIN SET @listitem = LTRIM(RTRIM(LEFT(@list, @cpos - 1))) SET @loop = @loop + 1 IF CHARINDEX(@substring,@listitem) > 0 RETURN @loop SET @list = RIGHT(@list, LEN(@list) - @cpos) SET @cpos = CHARINDEX(@delimiter, @list, 1) END END -- if we got here, the item was not found in the list RETURN 0 END GO |
Cool, many thanks for these scripts… 🙂 On SQL Server 2012, i had to replace the > with > and then i could install them without issues. great stuff! coldfusion 4 the win… 🙂