Forum Discussion

HarshaChilakala's avatar
HarshaChilakala
Copper Contributor
May 06, 2024
Solved

How to fetch??

How to fetch the customers names, who's having first letter CAPITAL ??

  • HarshaChilakala , by using a  CS = "Case Sensitive" collation, for example:

     

    ;WITH names AS
        (SELECT 'Smith' AS name UNION ALL
         SELECT 'Miller' UNION ALL
         SELECT 'taylor')
    SELECT *
    FROM names
    WHERE LEFT(name, 1) COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(LEFT(name, 1)) COLLATE SQL_Latin1_General_CP1_CS_AS

3 Replies

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    If the column is CHAR or VARCHAR type, sql like below will work:
    SELECT [customer_name] FROM [customer] WHERE ASCII( LEFT([customer_name], 1)) = ASCII( UPPER(LEFT([customer_name], 1)))
    or the column is NCHAR or NVARCHAR, you can use sql like this:
    SELECT [customer_name] FROM [customer] WHERE UNICODE( LEFT([customer_name], 1)) = UNICODE( UPPER(LEFT([customer_name], 1)))
    BUT, those sql might cause index ignored, SO MAY NOT fit on performance sensitive scene
  • olafhelper's avatar
    olafhelper
    Bronze Contributor

    HarshaChilakala , by using a  CS = "Case Sensitive" collation, for example:

     

    ;WITH names AS
        (SELECT 'Smith' AS name UNION ALL
         SELECT 'Miller' UNION ALL
         SELECT 'taylor')
    SELECT *
    FROM names
    WHERE LEFT(name, 1) COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(LEFT(name, 1)) COLLATE SQL_Latin1_General_CP1_CS_AS

Resources