Get String Composition

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')

*/

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')

*/