Post reply

Find All Records With A Lowercase Letter

  • June 30, 2005 at 11:14 am

    #89929

    OK, here is one that has me stumped!

    I have a table that has a column of alpha-numeric reference codes. I want to perform a select (and eventally, a delete) statement to find all records where this column contains a lowercase letter in the value.

    For Example:

    1     | ABC54321

    2     | abc48d56

    3     | 541ad885

    4     | 74AD56A

    5     | AbCd881

    I want to perform a search that would return only rows 2,3 and 5 in the example above. Just where a lowercase a-z is present.

    This will be a one time query/delete and I can not make any changes to the column properties including the Collation type.

    I would appreciate any assistance you guys can offer.

    Bob Gibilaro

  • June 30, 2005 at 11:35 am

    #570020

    First of all what is the colation set for the field on that table?

    Default is SQL_Latin1_General_Cp1_CI_AS

    Meaning Latin, General, CI = Case insensitive, AS = Ascending Sort If this is the case then.

    This will work, probly not very fast though, but that was not a requirement.

    Declare @a table (pk int identity, SomeCode varchar(10))

    insert into @a (SomeCode)

    values('ABC54321')

    insert into @a (SomeCode)

    values('abc48d56')

    insert into @a (SomeCode)

    values('541ad885')

    insert into @a (SomeCode)

    values('74AD56A')

    insert into @a (SomeCode)

    values('AbCd881')

    select *

    from @a

    where somecode like '%[abcdefghijklmnopqrstuvwxyz]%' COLLATE SQL_Latin1_General_CP1_CS_AS

    Great article about Case sensativity.

    http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

    If it is CS, then you just compare on the lower case letter.

    where Somecode like '%[abcdefghijklmnopqrstuvwxyz]%'

    Because case sensative collation manages case sensativity.

  • June 30, 2005 at 11:40 am

    #570024

    I am ashamed at how easy this was!

    I did not even have to use the table variable, like you suggested. I just queried against the live table and used the WHERE clause you provided. Worked like a charm!

    Thanks so much. It made my day.

    Bob Gibilaro

  • June 30, 2005 at 11:47 am

    #570030

    The table variable was simply for demo purpuses .

  • June 30, 2005 at 12:06 pm

    #570041

    I created a table variable and inserted some records so I could run the query to test for the correct results

    http://www.aspfaq.com/etiquette.asp?id=5006

  • July 1, 2005 at 12:48 am

    #570238

    You can also use

    where somecode != upper(somecode) COLLATE SQL_Latin1_General_CP1_CS_AS

     

  • July 1, 2005 at 12:03 pm

    #570435

    One comment:  in the LIKE clause, you could use '%[a-z]%' instead of typing the whole alphabet.  Not sure how that would change for non-english alphabets.

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

  • July 1, 2005 at 1:19 pm

    #570448

    I tried that before proposing upper(). I tried it using an english alphabet ... and it does not work with '%[a-z]%'. I do not understand why ...

     

    Bert

Viewing 8 posts - 1 through 7 (of 7 total)

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