CODEWITHSUNDEEP
sql
pruefsumme
pruefsumme

Reputation: 341

Select all table entries which have a fully capitalized string in a specific column?

I have a database table with a few thousand entries. A part of the entries (~20%) have been entered with a fully capitalized strings in the 'name' column.

Example:

id | name
---------
1 | THOMAS GOLDENBERG
2 | Henry Samuel
3 | GIL DOFT
4 | HARRY CRAFT
5 | Susan Etwall
6 | Carl Cooper

How would an SQL query look like that selects all entries with a fully capitalized string in the name column? (i.e. in the example: those with the ID 1,3,4)

Upvotes: 1

Views: 458

Answers (4)

Remus Rusanu
Remus Rusanu

Reputation: 294497

What database system?

In theory you can do a simple SELECT ... WHERE name = UPPER(name); but that does not always work. Depending on the collation of your data, you may found that all records satisfy this condition because the comparison used may be case insensitive.

You need to ensure you compare using a case sensitive collation, and the correct answer depends on the database platform you use. For example, using SQL Server syntax:

SELECT ... WHERE Name COLLATE Latin1_General_100_CS_AS = UPPER(Name);

This also works in MySQL with the condition that you use a collation name valid on MySQL.

Upvotes: 4

Mike Valenty
Mike Valenty

Reputation: 8991

Here's a MySql function to convert uppercase to title case:

example:

update your_table set name = tcase(name) where name = upper(name);

function:

CREATE FUNCTION `tcase`(str text) RETURNS text CHARSET latin1
    DETERMINISTIC
BEGIN

  DECLARE result TEXT default '';
  DECLARE space INT default 0;
  DECLARE last_space INT default 0;

  IF (str IS NULL) THEN
    RETURN NULL;
  END IF;    

  IF (char_length(str) = 0) THEN
    RETURN '';
  END IF;

  SET result = upper(left(str,1));
  SET space = locate(' ', str);    

  WHILE space > 0 DO            
    SET result = CONCAT(result, SUBSTRING(str, last_space+2, space-last_space-1));            
    SET result = CONCAT(result, UPPER(SUBSTRING(str, space+1, 1)));        
    SET last_space = space;
    SET space = locate(' ', str, space+2);    
  END WHILE;

  SET result = CONCAT(result, SUBSTRING(str, last_space+2));

  RETURN result;

END $$    
DELIMITER ;

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

select * from your_table where name = upper(name)

Upvotes: 2

Kaleb Brasee
Kaleb Brasee

Reputation: 51965

In MySQL it would be:

SELECT id FROM table WHERE name = UPPER(name);

I think this would work the same way in SQL Server, DB2 and Postgres.

Upvotes: 5

Related Questions