Detecting Cases using SQL statements

Question

I'm trying to use a SQL command that will 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?

Michael Berkowski · Answer 1 · 2011-06-09T18:39:41.360

7

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.

edited Jun 09 '11 at 18:39

answered Jun 09 '11 at 17:24

Michael Berkowski

267,341
46
444
390

SELECT column REGEXP '[A-Z]{4}' FROM table.column WHERE LST.column REGEXP '[A-Z]{4}' Does this make sense? I'm getting a syntax error – Aeonstrife Jun 09 '11 at 18:11
You're selecting `FROM table.column`. You only want `FROM table`. The WHERE clause is kind of unnecessary. – Michael Berkowski Jun 09 '11 at 18:18
@Aeonstrife You might want `SELECT column FROM table WHERE column REGEXP '[A-Z]{4}' = 1;` This returns the full text of matching rows in `column` – Michael Berkowski Jun 09 '11 at 18:19
'SELECT column REGEXP '[A-Z]{3}' FROM table' isn't working either. It still says there's a syntax error near [A-Z]{3} – Aeonstrife Jun 09 '11 at 18:20
OK that makes more sense to me. However, now the error is: _An expression of non-boolean type specified in a context where a condition is expected, near 'REGEXP'._ – Aeonstrife Jun 09 '11 at 18:25
@Aeonstrife Are you passing the whole query in enclosed in single-quotes as in your comment? If so, then it would break at the first regexp single-quote. `SELECT column REGEXP '[A-Z]{3}' FROM table;` should work. – Michael Berkowski Jun 09 '11 at 18:25
No that was just my attempt at formatting it for the comments. There are no single quotes around my queries – Aeonstrife Jun 09 '11 at 18:26
@Aeonstrife this is tagged mysql, but that error message only turns up MS SQL Server results for me. Are you on SQL Server? My syntax was for mysql. – Michael Berkowski Jun 09 '11 at 18:29
Yes I'm on an SQL server. Sorry I guess I should have mentioned that. I'm a little new at this – Aeonstrife Jun 09 '11 at 18:31
@Aeonstrife I don't think there's built-in support for regex in SQL Server. See here: http://stackoverflow.com/questions/3635150/how-do-i-select-a-regex-match-from-a-text-varchar-in-ms-sql I don't think I can assist further. – Michael Berkowski Jun 09 '11 at 18:40
Funny, people keep upvoting my answer even though it's totally invalid for the question since retagged from MySQL to SQL Server. – Michael Berkowski Jun 10 '11 at 13:47
Totally invalid is a bit harsh. RegExp is what should be used for this so in SQLServer you need to install a .NET assembly that provides the RegExp functionality. Something like this might work. http://msdn.microsoft.com/en-us/magazine/cc163473.aspx. – Mikael Eriksson Jun 10 '11 at 14:13

score 3 · Accepted Answer · answered Jun 10 '11 at 13:34

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

score 1 · Answer 3 · answered Jun 09 '11 at 17:24

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

score 0 · Answer 4 · answered Jun 22 '11 at 21:36

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.

Detecting Cases using SQL statements

4 Answers4