Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

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

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

  • Comments 1
  • Likes

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
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment