Microsoft SQL Function to Count Number of Characters in String

I have a DocuWare system that stores emails all day long and it will store everything including junk emails. I wanted to write a Microsoft SQL Trigger that check  those emails characters that comes from foreign countries (China and Russia, ect)  and look into the SUBJECT column and find how many ??? (Questions masks) are found and base on the number of occurrence I will set a flag in that document to be deleted at later time.

Sample header:
SUBJECT: ?e??am? ??? ?a???o ?pe??p?????

To accomplish this task I wrote a Microsoft SQL function that you can pass a String (in this case the SUBJECT column) and the character (in this case the ?) that you looking for and it will return the number of occurrences.  In my case if the number of questions marks found in that column is greater than 5 I will set the flag in this document to be delete with a trigger.

Here is T-SQL Function:

CREATE FUNCTION CountCharactersInString
@input_string nvarchar(max),
@single_character nchar(1),
@collation varchar(100)
DECLARE @count int
IF @collation IS NULL
SELECT @count = LEN(@input_string) -
LEN( REPLACE( @input_string, @single_character, ''))
ELSE IF @collation = N'Latin1_General_CI_AI'
SELECT @count = LEN(@input_string) -
LEN( REPLACE( @input_string COLLATE Latin1_General_CI_AI, @single_character COLLATE Latin1_General_CI_AI, '' ))
RETURN @count

You can also download the complete Microsoft SQL (T-SQL) from this location: function mssq CountCharactersInString.

Sample image of a simple SELECT statement in Microsoft SQL Server Management Studio:

TSQL Example Function

TSQL Example Function

About onlinejt



No comments yet.

Leave a Reply