Two Programming Tricks: Counting Characters In A String and RETURN CASE


A couple of tricks here, from some recent work: a user-defined function to count the number of characters in a string, and a new (for me) syntax discovery: RETURN CASE.


Here’s what I came up with for the user-defined function:


CREATE FUNCTION dbo.fnCountCharactersInString
(
   
@StringToTest nvarchar(max),
   
@CharactersToCount nvarchar(max)
)
RETURNS int
AS

BEGIN

   
DECLARE @EditableCharacterCount int,
            
@Looper int

   
SELECT @EditableCharacterCount = 0,
          
@Looper = 1


— here’s a readable version of this code for documentation purposes.
— the code below is functionally identical, but performs faster
—   WHILE @Looper <= LEN(@StringToTest)
—       BEGIN
—           SELECT @EditableCharacterCount = @EditableCharacterCount +
—               CASE
—                   WHEN CHARINDEX (SUBSTRING (@StringToTest, @Looper, 1), @CharactersToCount) > 0
—                       THEN 1
—                   ELSE 0
—               END
—           SET @Looper = @Looper + 1
—   END

   
WHILE @Looper <= LEN(@StringToTest)


        SELECT @EditableCharacterCount = @EditableCharacterCount +
              
CASE
                  
WHEN CHARINDEX (SUBSTRING (@StringToTest, @Looper, 1), @CharactersToCount) > 0
                       
THEN 1
                  
ELSE 0
              
END,
              
@Looper = @Looper + 1

    
RETURN @EditableCharacterCount
END
GO


So, this query..


SELECT PhoneMask, dbo.fnCountCharactersInString (PhoneMask, ‘9#’) FROM MyTable


.. might produce these results..


PhoneMask      (No column name)
(###)###-####  10
#-####-###     8
##-###-####    9
##-###-###9    9


In the production implementation of this work, I stumbled upon a nifty piece of syntax, RETURN CASE.  I needed to translate one potential value in my result set to another.  I tried this, and it worked:


RETURN
    CASE @EditableCharacterCount
       
WHEN 0 THEN 64
       
ELSE @EditableCharacterCount
    END


This works exactly as one would hope it would.  If the count of editable characters is 0, the value 64 is returned; otherwise, the true count is returned.  Another nifty implementation from the SQL team.

Comments (0)