At Simple-Talk we're always looking to provide useful tools, workbenches, scripts and so on. I invite you to submit nominations for our "most useful user-defined function" competition.

 

If there is a routine you wish someone would "invent" for you - something that would remove some of the pain from a painstaking task - then let us know! Maybe a 'string slicer', a function to verify 'true-false' values, validate numbers, or anything else that would make your life easier. We'll put our team of Simple-Talk experts on the case and see what we can come up with!

 

Of course, if you've written a function or procedure that you've found incredibly useful and you're willing to share it, then please do post it to this forum, and whet people's appetites!

 

The three best nominations/submissions will win a copy of Phil Factor's sensational memoirs, Confessions of an IT Manager and a Simple-Talk goodie bag. Five runners up will receive the latter.

 

Cheers,

Tony.

 

 

---

Tony Davis
Simple-Talk editor-in-chief
editor@simple-talk.com
[blog] [articles]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[ufsExtractFromVarchar] 

/* This is a little function that I use for 'ad hoc' parsing

of things like numbers or variables from strings. In SQL Server

2005 one would make it a varchar(MAX). What makes this versatile

is that you can specify what to search for as the start of the 

substring (e.g. a number) and what then constitutes the end after

that (e.g. not-a-number). Once can specify whether it should be

the first such thing, or whatever.

I give a few examples and results as a regression-test harness

at the end of the function.*/

( 

@string VARCHAR(8000),

@WhichOne INT,

@StartRegex VARCHAR(255)='%[0-9]%',

@EndRegex VARCHAR(255)='%[^.0-9]%'

) 

RETURNS VARCHAR(50)

AS 

BEGIN 

DECLARE 

       @Position INT,   --index of current search

       @WhereWeAre INT,--index into string so far

       @instancecount INT,--the number of instances found so far

       @next INT,       --where the next search starts 

       @size INT,       --the total size of the text

       @Instance VARCHAR(50) --the string found



SELECT @String=COALESCE(@String,'')

SELECT         @instance='0',

               @WhereWeAre=1,

               @size=LEN(@string),

               @Position=1,

               @instancecount=0

WHILE @Position>0 AND @instancecount<@WhichOne

       BEGIN

       SELECT @Position=PATINDEX(@StartRegex,

                               SUBSTRING(@string,@whereWeAre,8000))

       IF @Position>0 

               BEGIN

               SELECT @next=@WhereWeAre+@Position,

                       @instancecount=@instancecount+1

               SELECT @Position=PATINDEX(@EndRegex,

                               SUBSTRING(@string,@next,8000)+' ')

               SELECT @WhereWeAre=@next+@Position,@instance=

                               SUBSTRING(@String,@next-1,@position)

               END

       END

RETURN @instance

END

/*

Select dbo.ufsExtractFromVarchar('Here is your Order number:

           35939

        Please check on the status',1,default,default)

--35939

Select dbo.ufsExtractFromVarchar(

       'There is no number here',1,'%[A-Z]%','%[^A-Z''-]%')

--There

Select dbo.ufsExtractFromVarchar(

       'There is no number here',2,'%[A-Z]%','%[^A-Z''-]%')

--is

Select dbo.ufsExtractFromVarchar(

       'There is no number here',3,'%[A-Z]%','%[^A-Z''-]%')

--no

Select dbo.ufsExtractFromVarchar(

       'Somewhere there is a $variable here',

               1,'%$[a-z]%','%[^A-Z_-]%')

--$variable

Select dbo.ufsExtractFromVarchar('Somewhere I believe 

                       there is a $variable=345 here',

               1,'%$[a-z]%','%[^A-Z0-9=_-]%')

--$variable=345

*/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF

GO



/*------------------------------------------------------*/

ALTER  function [dbo].[ufsEscape]

/* Mine is a simpler function than Phil's. It is 

a little thing that shows the HEX values of any

characters that aren't obvious.

It was originally used to 'escape' the 'query string' of

URLs but proved to be useful just to peek at strings

that were causing trouble. (for URLs, you will need to

take the space character out of the wildcard.*/

( 

@String VARCHAR(8000)

) 

RETURNS VARCHAR(8000) AS 

BEGIN 



DECLARE @ii INT

DECLARE @iiMax INT

DECLARE @ch CHAR

DECLARE @Output  VARCHAR(255)



SELECT @Output ='', @ii=1, @iiMax=LEN(@String)



WHILE @ii<=@iiMax

       BEGIN

       SELECT @ch=SUBSTRING(@String,@ii,1)

       IF @ch LIKE '[a-z0-9 ]'

               SELECT @Output=@output+@ch

       ELSE

               SELECT @Output=@output+'%'

                       + SUBSTRING ('0123456789ABCDEF',

                       (ASCII(@ch) / 16)+1,1)

                       +  SUBSTRING ('0123456789ABCDEF',

                       (ASCII(@ch) % 16)+1,1)

       SELECT @ii=@ii+1

       END

RETURN (@Output) 

END 



/* now to test it out.....

select dbo.ufsEscape(' This works well

')

--%09This works well%0D%0A

*/

I'll nominate some from my archive...

Tokenize
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/tokenize-udf.aspx

ReflectRoutine
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/reflect-a-tsql-routine.aspx

Pattern-based SplitString
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/pattern-based-split-string.aspx

Substring occurrence count
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/counting-occurrences-of-a-substring-within-a-string.aspx

Unlimited-length SplitString
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/splitting-a-string-of-unlimited-length.aspx

Pattern-based replacement for REPLACE function
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/pattern-based-replacement-udf.aspx

... enjoy :)

---

Looking for an advanced SQL Server 2005 book?

Expert SQL Server 2005 Development

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
Function fn_DatePart
Written by Casey Hart
Use, edit, alter, or share at will, but do so at your own risk. 

I built this because I hate doing the datepart dance to make strings.

This returns a table of dateparts that correspond to the the datetime  
parameter passed to it, or for getdate() if null is passed.  It returns all
date parts in their native data types, as well as their string equivalents. 
I also added month, month abbr., day, ampm, day of week, suffix
(st, nd, rd, th), and a couple common variations of the complete time for good
measure.  

One gotcha: 
Since UDFs won't accept a system function as a default parameter, and you can't
call a system function (i.e. getdate()) within a UDF, getting this to return
the current date without @indate=getdate() before the function call required a
little trickery. 

UDFs CAN reference views, and views CAN call system functions, so it does the
semantic ring-around-the-rosy when a null is passed to fn_Datepart as the
@indate parameter.  In such cases, it uses a simple view called v_getdate in
the current database to get its date.  If you want this behavior, you will need
to create the view. 
 e.g.  create view v_getdate as select getdate() as indate
 

Usage with a datetime variable:
     DECLARE @indate datetime
     SET @indate='Dec 3 2001'
  SELECT day_s + ' ' + month_s + ' the ' + dw_s + ' ' + yyyy_s as dstr
  FROM dbo.fn_DatePart( @indate )
  
    dstr
    ----
    Monday December the 3rd 2001

As a correlated subquery:
  SELECT email, (select dd_s + hh_s + mi_s FROM dbo.fn_datepart(join_dt))
  FROM members
  
  
*/
CREATE FUNCTION [dbo].[fn_DatePart]( @indate datetime = null)
RETURNS @dateparts TABLE
(
    yyyy int
    , yyyy_s varchar(4)
    , qq tinyint
    , qq_s varchar(2)
    , mm tinyint
    , mm_s varchar(2)
    , monthabbr_s varchar(4)
    , dy int
    , dy_s varchar(3)
    , dd tinyint
    , dd_s varchar(2)
    , wk int
    , wk_s varchar(2)
    , dw tinyint
    , dw_s varchar(2)
    , hh tinyint
    , hh_s varchar(2)
    , hh24 tinyint
    , hh24_s varchar(2)
    , mi tinyint
    , mi_s varchar(2)
    , ss tinyint
    , ss_s varchar(2)
    , ms int
    , ms_s varchar(3)
    , ampm_s varchar(2)
    , time24_s varchar(12)
    , time_s varchar(14)
    , month_s varchar(9)
    , dw_suffix varchar(2)
    , day_s varchar(9)
)
AS

BEGIN
    IF @indate IS NULL
        SELECT @indate = indate FROM v_getdate

    INSERT INTO @dateparts
    SELECT datepart( yyyy, @indate)                                       yyyy
        , convert( varchar(4), datepart( yyyy, @indate ))                yyyy_s
        , datepart( qq, @indate)                                          qq
        , convert( varchar(2), datepart( qq, @indate) )                   qq_s
        , datepart( mm, @indate)                                          mm
        , right('0' + convert( varchar(2), datepart( mm, @indate)), 2)    mm_s
        , convert( varchar(3), @indate, 100)                        monthabbr_s
        , datepart( dy, @indate)                                          dy
        , convert( varchar(3), datepart( dy, @indate) )                   dy_s
        , datepart( dd, @indate)                                          dd
        , right('0' + convert( varchar(2), datepart( dd, @indate)), 2)    dd_s
        , datepart( wk, @indate)                                          wk
        , convert( varchar(2), datepart( wk, @indate) )                   wk_s
        , datepart( dw, @indate)                                          dw
        , convert( varchar(2), datepart( dw, @indate) )                   dw_s
        , convert( int, left( right(convert( varchar(40), dateadd(hh, -1, @indate), 109), 14), 2)) hh
        , right(replace(left( right(convert( varchar(40), dateadd(hh, -1, @indate), 109), 14), 2), ' ', '0'), 2) hh_s
        , convert(tinyint, convert( varchar(2), @indate, 14))              hh24
        , right( '0' + convert( varchar(2), @indate, 14), 2)             hh24_s
        , datepart( mi, @indate)                                             mi
        , right( '0' + ltrim( convert( varchar(2), datepart( mi, @indate) )), 2)  mi_s
        , datepart( ss, @indate)                                             ss
        , right( '0' + ltrim( convert( varchar(2), datepart( ss, @indate) )), 2)  ss_s
        , datepart( ms, @indate)                                             ms
        , right( '00' + ltrim( convert( varchar(3), datepart( ms, @indate) )), 3) ms_s
        , Right(RTrim(convert(varchar(40), @indate, 100)), 2)            ampm_s
        , left( convert( varchar(20), @indate, 14), 12)                time24_s
        , right( '0' + ltrim( reverse( left( reverse( convert( varchar(40), @indate, 109)), 14) )), 14) time_s
        , CASE datepart( mm, @indate)
            When 1  THEN 'January'
            When 2  THEN 'February'
            When 3  THEN 'March'
            When 4  THEN 'April'
            When 5  THEN 'May'
            When 6  THEN 'June'
            When 7  THEN 'July'
            When 8  THEN 'August'
            When 9  THEN 'September'
            When 10 THEN 'October'
            When 11 THEN 'November'
            When 12 THEN 'December'
          END month_s
        , CASE datepart( dd, @indate)
            When 1      Then 'st'
            When 21     Then 'st'
            When 31     Then 'st'
            When 2      Then 'nd'
            When 22     Then 'nd'
            When 3      Then 'rd'
            When 23     Then 'rd'
            Else                                          'th'
          END dw_suffix
        , CASE ( datepart( dw, @indate ) + @@datefirst) % 7
            When 1  THEN 'Sunday'
            When 2  THEN 'Monday'
            When 3  THEN 'Tuesday'
            When 4  THEN 'Wednesday'
            When 5  THEN 'Thursday'
            When 6  THEN 'Friday'
            When 7  THEN 'Saturday'
          END day_s   
RETURN
END

why use datepart( mm, @indate) and not datename(month, @indate) ?
At least datename will be language sensitive

Sorry, did not mean to find fault in the function, but I tried to run using the sample script
     DECLARE @indate datetime
     SET @indate='Dec 3 2001'
  SELECT day_s + ' ' + month_s + ' the ' + dw_s + ' ' + yyyy_s as dstr
  FROM dbo.fn_DatePart( @indate )

the result i get is
dstr                           
-------------------------------
Monday December the 2 2001

which is diff from what you posted there.

• concat() aggregate - i almost melted when i realized that .net in sql server would allow me to build a custom text aggregate ... and then i knew it pure goodness when i found the baseline implementation right there in the "msdn2" online help... i named mine "concat"... it's great for the pivot queries we do on our product catalog site where we go from an open schema product attribute model to display a grid of sku's down the left, attribute names across the top and the cells contain the attribute descriptions for each sku... aggregating is necessary when there is multiple attribute values for the same sku+attribute...here's one of those 1000 word pictures :) notice the comma delimitted list of "fits models" that is thanks to our "concat" aggregate: http://www.lesco.com/ProdCatalog/ProdCatalogNav.aspx?SKUGroupID=312  here's the msdn2 url for now... there's some good tweaks to be made of course... filtering out nulls and blanks comes to mind: http://msdn2.microsoft.com/en-us/library/ms254508.aspx ... look for "CLR User-Defined Aggregates"
• "squish" - what we named a string routine that filters out all spaces and non alpha-numerics... makes for more efficient searches like when trying to get a hit on "phone number" and the like ... one must design up front to store the "squished" version along side what was actually entered... then you squish your search input as well and go after the squished version in your query
• regex_match - yet another delicious meltdown when i could finally use the full .net regex matching syntax to suck stuff out of complex varchars
• regex_replace - natural followup to the former... bout time replace was more flexible!
• split - love this for taking the ad hoc list of customer account numbers, product numbers, whatever out of an email and "splitting" it on char(13) to produce a joinable list of values 

i can't find my brain download cable atm but if there's interest i'll post source

Watch out for that .NET concat() aggregate.  The 4000-character limit is pretty painful, and perf isn't too good either.  Better to use FOR XML PATH.  Here's a simple example in AW:

SELECT
    Name + ', ' AS [data()]
FROM Production.Product
FOR XML PATH('')

Using that as a subquery you can get the same results as the aggregate but with better perf and no 4000-character limit.  There is another way to solve the UDAgg problem but you'll have to wait for my new book to find out :)  (see chapter 6!)

---

Looking for an advanced SQL Server 2005 book?

Expert SQL Server 2005 Development