none
SP for Check Existing Some Char in Col (Returns False / True)

    Question

  • Hello Friends,
    I am looking for a SP you can check values ​​within a field.

    Parameters: 
    ASCII code value (from) 
    ASCII code value (until) 

    Output parameters: 
    Boolean output (whether present or not) 
    Is one of the characters in a cell contains the ASCII code set (It should scan all the value in a cell)


    Example: testing whether the cell contains low letter / big / Number / complex characters


    Tanks
    Sunday, April 13, 2014 11:14 AM

Answers

  • COLLATE is indeed the way to go, but John did the mistake of using a CS_AS collation where a range like a-z includes all uppercase letters, save one of A and Z (I don't remember which).

    Use Latin1_General_BIN2 instead. In a binary collation, characters sort according to their code point.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 20, 2014 12:13 PM

All replies

  • if you class special characters as anything NOT alphanumeric:

    DECLARE @MyString VARCHAR(100)
    SET @MyString = 'adgkjb$'
    
    IF (@MyString LIKE '%[^a-zA-Z0-9]%')
        PRINT 'Contains "special" characters'
    ELSE
        PRINT 'Does not contain "special" characters'

    Just add to other characters you don't class as special, inside the square brackets

    Source: http://stackoverflow.com/questions/2558755/how-to-detect-if-a-string-contains-special-characters

    Sunday, April 13, 2014 11:19 AM
  • Based on the description you provided, you can probably implement a simpler solution, no SP needed, using PATINDEX and it's ability to preface a wildcard with "NOT" via "^".  (Trying to match on presence, rather than absence won't work, because some strings will have both valid and unwanted characters.  Try this.

    Setup:  
    		Declare @BlackList Varchar(99) = '%[^-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'' *,]%'
    		Declare @Example Table (stringdata varchar(99))
    		Insert @Example select 'alphabetic only'
    		UNION Select 'alphabetic and 1 number'
    		UNION Select '123456' /* numbers only */
    		UNION Select 'Letters, Numbers, and Special Characters!'
    
    Method1_Patindex:  --
    		Select *
    			, patindex(@BlackList, Stringdata) /* Simplest way */
    			, Case When PatIndex(@Blacklist, StringData) > 0 Then 1 Else 0 End as Boolean_Answer
    		  From @Example

    The "SETUP" section is just setting the stage for the SQL at the end, only the last SQL statement represents the answer you might want.

    Output:

    stringdata    ColPos    Boolean_Answer
    123456    1    1
    alphabetic and 1 number    16    1
    alphabetic only    0    0
    Letters, Numbers, and Special Characters!    41    1

    Sunday, April 13, 2014 11:31 AM
  • Also, to specifically answer the question you asked about a range of ASCII characters, here's a way using a NUMBERS table (suggestion: create a permanent NUMBERS table, Bing it to see how). 

    Setup:
    		Declare @Example Table (stringdata varchar(99))
    		Insert @Example select 'alphabetic only'
    		UNION Select 'alphabetic and 1 number'
    		UNION Select '123456' /* numbers only */
    		UNION Select 'Letters, Numbers, and Special Characters!'
    		UNION Select 'text and unprintable character' + Char(10)
    
    Setup_2:
    		Declare @Numbers Table (Number Smallint)
    		Insert @Numbers select NUMBER from Master..SPT_VALUES where type = 'p' and number > 0
    
    		Declare @FromAscii int, @ToAscii int
    		Select @FromAscii = 32, @ToAscii = 125
    
    		Select Stringdata, Coalesce(FoundInd, 0)
    		  From @Example
    		  Outer Apply 
    			  (Select 1 as FoundInd where Exists  
    					(Select 'x' from @Numbers
    					  where NOT (Ascii( SubString(StringData, Number, 1) ) Between @FromAscii and @TOASCII )
    					)  
    				) Label_for_Apply
    Notice that this is longer and probably less convenient than PATINDEX:  For example, if you were looking for just letters, the ASCII codes are sequential, but codes 91 through 96 interrupt that convenient sequence between uppercase and lowercase letters, so you'd have to account for that.
    Sunday, April 13, 2014 12:05 PM
  • Tanks for your script !

    I cut it change depending on what I need (small signal, large number NOT COMPLEX CHAR). 
    But he is still looking for a case (such as! @ # $% ^ & * () _ +) 

    I was looking for Is it by ASCII numbers but there is no connection, do you know where I am wrong or have set this up correctly?

    --------------------------------------------------------

    -- Script for Check Password Complex 
    -- Min 5 Char, 1 char need to be Big, Small, And Number.
    ---------------------------------------------------------

    Declare @mystring varchar(10)
    set @mystring='aBaaa11'
    print len(@mystring)

    If len(@mystring) >= 5  
    BEGIN
    if (@MyString LIKE '%[^a-zA-Z0-9]%')
    print 'Congratulations Your password is valid (Special Characters)'
    else
    print 'Password Policy Need to be Least 5 characters (uppercase lowercase letter and number)'

    END

    Else if  len(@mystring)  <= 4
    BEGIN
        print 'Password Policy Need to be Least 5 characters Minimum !'
    END

    Saturday, April 19, 2014 10:01 AM
  • You would need to do a count for all 3 or 4 types of characters separately, then check to see if all 3 (some places allow any 3 of the 4) types.  This example does it (optionally, uncomment the check for special characters too, depending on your requirements).  (Notice the "Collate" statement, needed for comparing upper or lower case).

    Declare @mystring varchar(10)
    set @mystring='aBaaa11'
    print len(@mystring)
    
    If len(@mystring) >= 5  
    BEGIN
    if (@MyString  COLLATE Latin1_General_BIN2 LIKE '%[A-Z]%'
    	AND @MyString  COLLATE Latin1_General_BIN2 LIKE '%[a-z]%'
    	AND @MyString LIKE '%[0-9]%'
    	/* AND @MyString Like  '%[!@#$%^&*('')X_+-={}X[X]|\:;"<>,./?]%' Escape 'X'  */
    	)
      
    print 'Congratulations Your password is valid (Special Characters)'
    else
    print 'Password Policy Need to be Least 5 characters (uppercase lowercase letter and number)'
    
    END
    
    Else if  len(@mystring)  <= 4
    BEGIN
        print 'Password Policy Need to be Least 5 characters Minimum !'
    END
    EDIT: Corrected collation, thanks Erland.  


    • Proposed as answer by HimanshuSharma Monday, April 21, 2014 9:51 AM
    • Edited by johnqflorida Monday, April 21, 2014 2:00 PM Added correction per Erland
    Saturday, April 19, 2014 3:24 PM
  • Thank you, I realized my mistake. 

    But still the script you wrote it does not distinguish between uppercase and lowercase letters in English (even Use the COLLATE). 

    Do you know the reason or ways to solve this? 
    Numbers and letters demanding, but no matter whether it is small or large char.

    Sunday, April 20, 2014 9:45 AM
  • COLLATE is indeed the way to go, but John did the mistake of using a CS_AS collation where a range like a-z includes all uppercase letters, save one of A and Z (I don't remember which).

    Use Latin1_General_BIN2 instead. In a binary collation, characters sort according to their code point.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 20, 2014 12:13 PM