Thursday, October 21, 2010

Find rows with lower case characters in SQL Server

Table - Test
Column - col1

SELECT *
FROM Test
WHERE col1
LIKE '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS

4 comments:

  1. I have a product name table with products names collected both in upper and lower cases. I want to pull out product names that start with lower/uppper cases.

    Can this help ?

    ReplyDelete
  2. Hi Mayukaha,

    The aforementioned query is to return rows when atleast one lower character present in the value.

    For your requirement, try the following queries. I've just used "product_name" as the tbale name and "name" as the column name
    1) Find product names start with an upper case character

    select * from product_name where
    LEFT(name,1) = UPPER(LEFT(name,1)) COLLATE Latin1_General_CS_AS

    2) Find product names start with an lower case character

    select * from product_name where
    LEFT(name,1) = LOWER(LEFT(name,1)) COLLATE Latin1_General_CS_AS

    ReplyDelete
  3. Or you could change slightly (remove the first %) to achieve the same result.
    SELECT *
    FROM Test
    WHERE col1
    LIKE '[abcdefghijklmnopqrstuvwxyz]%'
    collate Latin1_General_CS_AS

    ReplyDelete