Post reply

Field with mixed case characters

  • February 20, 2004 at 10:36 am

    #107740

    Does anybody have a script to check if a character field in a table has mixed case characters, e.g. AbCdE ?

  • February 20, 2004 at 3:40 pm

    #495013

     CREATE FUNCTION dbo.udf_IsMixedChars(@str varchar(4000))

     Returns int

     AS

     BEGIN

    -- This Function Assumes the Existence of a Numbers Table

    -- CREATE TABLE Numbers(Number int) Filled accordingly!

     DECLARE @Result int

    If Exists(

     Select 1

     From Numbers

     Where Number <= Len(@str)

     and  ASCII(Substring( @str, Number,1)) between 65 and 90

    )

    and

    Exists ( Select 1

     From Numbers

     Where Number <= Len(@str)

     and  ASCII(Substring( @str, Number,1)) between 97 and 122

    )

    SET @Result = 1

    ELse

    SET @Result = 0

    RETURN @Result

    END

    Then Use the Fuction as

    SELECT *

    FROM TableName

    Where dbo.udf_IsMixedChars(fld) = 1

     

    HTH

     


    * Noel

  • February 23, 2004 at 1:21 am

    #495098

    That's fantastic! Many thanks...

  • February 23, 2004 at 12:42 pm

    #495207

    No need for a function.  If the column is longer than 30 characters, then adjust the length of the varbinary type being casted.

    SELECT CASE WHEN CAST(Col AS varbinary)=CAST(UPPER(Col) AS varbinary) OR CAST(Col AS varbinary)=CAST(LOWER(Col) AS varbinary) THEN 'No' ELSE 'Yes' END



    --Jonathan

  • February 24, 2004 at 3:57 am

    #495268

    That's even better Jonathan, Many thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply