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
-- 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
So, this query..
SELECT
.. 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.