Credits:
Author: Unknown/ChillyDBA
Date: 20 Jun 2012
Description
This function parses a character string and returns a count of the types of characters that make up the string.
Characters are currently split into 4 categories:
- Alphabetic
- Numeric
- Special (e.g. punctuation)
- Non-Printable (e.g CR, LF)
The function may be easily extended to produce additional counts.
There are 2 versions of the function:
- SCALAR function which utilises a loop and which will work across all versions of SQL Server
- TABLE VALUED function which makes use of CTE and the function udf_NumbersTable() to parse the string in one go and which will only work from SQL Server 2005 onwards.
Code
SCALAR function (SQL Server - all versions):
DROP FUNCTION dbo.udf_GetStringComposition
GO
CREATE FUNCTION dbo.udf_GetStringComposition (@StringToCheck VARCHAR(255)) RETURNS VARCHAR(255)
AS
BEGIN
/********************************************************
Purpose: To summarise the types of character contained within a string
Characters are considered to be in one of 4 categories:
Alphabetic
Numeric
Special (punctuation etc)
Non-Printable (CR, LF etc)
Process uses a loop through the input string one character at a time.
Will work on all SQL Server Versions
Author: Unknown
History: ?
*********************************************************/
DECLARE
@StringLength INT,
@Counter INT,
@AplhaCount INT,
@NumericCount INT,
@SpecialCount INT,
@NonPrintCount INT,
@StringComposition VARCHAR(255)
SELECT
@StringLength = DATALENGTH(@StringToCheck),
@Counter = 1,
@StringComposition = '',
@AplhaCount = 0,
@NumericCount = 0,
@SpecialCount = 0,
@NonPrintCount = 0
WHILE @Counter <= @StringLength
BEGIN
IF SUBSTRING(@StringToCheck, @Counter, 1) LIKE '%[a-zA-Z]%' SELECT @AplhaCount = @AplhaCount + 1
ELSE IF SUBSTRING(@StringToCheck, @Counter, 1) LIKE '%[!@#$%^&*(){}:";,.<>/?|\+-*]%' SELECT @SpecialCount = @SpecialCount + 1
ELSE IF SUBSTRING(@StringToCheck, @Counter, 1) LIKE '%[0-9]%' SELECT @NumericCount = @NumericCount + 1
ELSE SELECT @NonPrintCount = @NonPrintCount + 1
SELECT @Counter = @Counter + 1
END
IF @AplhaCount > 0
SELECT @StringComposition = @StringComposition + ';' + CAST(@AplhaCount AS VARCHAR(8)) + ' Characters'
IF @NumericCount > 0
SELECT @StringComposition = @StringComposition + ';' + CAST(@SpecialCount AS VARCHAR(8)) + ' Numerals'
IF @SpecialCount > 0
SELECT @StringComposition = @StringComposition + ';' + CAST(@NumericCount AS VARCHAR(8)) + ' Special'
IF @NonPrintCount > 0
SELECT @StringComposition = @StringComposition + ';' + CAST(@NonPrintCount AS VARCHAR(8)) + ' Non-Printable'
RETURN @StringComposition
END
/*
select dbo.udf_GetStringComposition('12345678')
select dbo.udf_GetStringComposition('12345678@')
select dbo.udf_GetStringComposition('12345678A')
select dbo.udf_GetStringComposition('12345678A@')
select dbo.udf_GetStringComposition('12345678 ')
select dbo.udf_GetStringComposition('12345678 ')
select dbo.udf_GetStringComposition('12345678 @')
select dbo.udf_GetStringComposition('12345678 @A')
select dbo.udf_GetStringComposition('12345678 A')
*/ |
TABLE VALUED function (SQL Server 2005 onwards):
|
DROP FUNCTION dbo.udf_GetStringComposition2
GO
CREATE FUNCTION dbo.udf_GetStringComposition2 (@StringToCheck VARCHAR(255))
RETURNS @CharCount TABLE
(
#AlphaChars INT,
#NumericChars INT,
#SpecialChars INT,
#NonPrintChars INT
)
AS
/********************************************************
Purpose: To summarise the types of character contained within a string
Characters are considered to be in one of 4 categories:
Alphabetic
Numeric
Special (punctuation etc)
Non-Printable (CR, LF etc)
Process uses a numbers table to parse the string in one pass.
Will work from SQL Server 2005 onwards.
Author: ChillyDBA
History: 20 Jun 2012
*********************************************************/
BEGIN
INSERT @CharCount
(
#AlphaChars,
#NumericChars,
#SpecialChars,
#NonPrintChars
)
SELECT
SUM(r.AlphaCount) AS #AlphaChars,
SUM(r.NumericCount) AS #NumericChars,
SUM(r.SpecialCount) AS #SpecialChars,
SUM( CASE
WHEN r.AlphaCount + r.NumericCount + r.SpecialCount = 0 THEN 1
ELSE 0
END
) AS #NonPrintChars
FROM
(
SELECT
CASE
WHEN SUBSTRING(@StringToCheck, value, 1) LIKE '%[a-zA-Z]%' THEN 1
ELSE 0
END AS AlphaCount,
CASE
WHEN SUBSTRING(@StringToCheck, value, 1) LIKE '%[!@#$%^&*(){}:";,.<>/?|\+-*]%' THEN 1
ELSE 0
END AS SpecialCount,
CASE
WHEN SUBSTRING(@StringToCheck, value, 1) LIKE '%[0-9]%' THEN 1
ELSE 0
END AS NumericCount,
0 AS NonPrintCount
FROM udf_NumbersTable (1, DATALENGTH(@StringToCheck))
) AS r
RETURN
END
/*
select * from dbo.udf_GetStringComposition2('12345678')
select * from dbo.udf_GetStringComposition2('12345678@')
select * from dbo.udf_GetStringComposition2('12345678A')
select * from dbo.udf_GetStringComposition2('12345678A@')
select * from dbo.udf_GetStringComposition2('12345678 ')
select * from dbo.udf_GetStringComposition2('12345678 ')
select * from dbo.udf_GetStringComposition2('12345678 @')
select * from dbo.udf_GetStringComposition2('12345678 @A')
select * from dbo.udf_GetStringComposition2('12345678 A')
*/
|
 Updating... Andy Hughes, Jun 20, 2012, 7:39 AM Andy Hughes, Jun 20, 2012, 7:39 AM
|
