Cool Tips‎ > ‎Data Type‎ > ‎

Data Type - Get String Composition

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

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:
  1. SCALAR function which utilises a loop and which will work across all versions of SQL Server
  2. 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')

*/



ċ
udf_GetStringComposition.sql
(2k)
Andy Hughes,
Jun 20, 2012, 7:39 AM
ċ
udf_GetStringComposition2.sql
(2k)
Andy Hughes,
Jun 20, 2012, 7:39 AM
Comments