Detecting Cases using SQL statements

I'm trying to use a SQL command that 开发者_如何学Gowill look through a block of text and determine if it has 3 consecutive uppercase letters in it. Is there a way of doing this? Or even simpler, is there a way that SQL can detect case?

EDIT Question was originally tagged mysql and this would've worked. It's been re-tagged as sql-server and this isn't valid for MS SQL Server.

You could use a REGEXP

SELECT columnname REGEXP '[A-Z]{3}' FROM table;

Returns 1 where columnname matches.

A function you can use

create function ThreeUpperInARow(@s varchar(max)) returns bit
begin
  declare @Rows int

  ;with cte as
  (
    select left(@s, 3) as Part,
           stuff(@s, 1, 1, '') as Rest
    union all
    select left(Rest, 3) as Part,
           stuff(Rest, 1, 1, '') as Rest
    from cte
    where len(Rest) >= 3
  )
  select @Rows = count(*)
  from cte
  where upper(Part) = Part COLLATE Latin1_General_CS_AS

  return case @Rows when 0
           then 0
           else 1
         end
end

Usage:

declare @T table(ID int identity, Txt varchar(max))
insert into @T
select 'aaaAFAaaaBB' union all
select 'aaaAAaaaBB'

select T.ID,
       T.Txt,
       dbo.ThreeUpperInARow(T.Txt) as Upp
from @T as T

Result:

ID          Txt             Upp
----------- --------------- -----
1           aaaAFAaaaBB     1
2           aaaAAaaaBB      0

To expand on Michael's answer: you can do a case sensitive comparison using this construct:

SELECT 'abc' LIKE BINARY 'ABC';  /*false*/

SELECT 'aBc' LIKE BINARY 'aBc'; /*true*/

See: http://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html
and: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

I've successfully compared uppercase by casting varchars as varbinary, since uppercase and lowercase compare as equal in SQL

SELECT * FROM Table WHERE CAST(SUBSTRING(ColumnName,1,3) as varbinary) = CAST(SUBSTRING(UPPER(ColumnName), 1,3) as varbinary)

One problem with this is that if you have a name like O'Reilly, it will return true.