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

  • Article

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.