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)

Skip to main content