CODEWITHSUNDEEP
t-sql
user2332607
user2332607

Reputation: 109

T-SQL find string with lowercase and uppercase

I have a database with several tables and I need to search every varchar column across the database, for columns that simultaneously contain lower and upper case characters.

To clarify:

If one column contains helLo the name of the column should be returned by the query, but if the column values only contain either hello or HELLO then the name of the column is not returned.

Upvotes: 6

Views: 15774

Answers (4)

S3S
S3S

Reputation: 25152

You can check the hash compared to its upper and lower values... here is a simple test:

declare @test varchar(256)
set @test = 'MIX' -- Try changing this to a mix case, and then all lower case

select case
            when hashbytes('SHA1',@test) <> hashbytes('SHA1',upper(@test)) and hashbytes('SHA1',@test) <> hashbytes('SHA1',lower(@test))
            then 'MixedCase'
            else 'Not Mixed Case'
        end

So using this in a table... you can do something like this

create table #tempT (SomeColumn varchar(256))
insert into #tempT (SomeColumn) values ('some thing lower'),('SOME THING UPPER'),('Some Thing Mixed')

SELECT SomeColumn
FROM #tempT
WHERE 1 = case
            when hashbytes('SHA1',SomeColumn) <> hashbytes('SHA1',upper(SomeColumn)) and hashbytes('SHA1',SomeColumn) <> hashbytes('SHA1',lower(SomeColumn)) then 1
            else 0
          end

Upvotes: 0

PM 77-1
PM 77-1

Reputation: 13344

Let's exclude all UPPER and all LOWER, the rest will be MIXED.

SELECT someColumn
FROM someTable
WHERE someColumn <> UPPER(someColumn) AND someColumn <> LOWER(someColumn)

EDIT:

As suggested in comments and described in detail here I need to specify a case-sensitive collation.

SELECT someColumn
FROM someTable
WHERE someColumn <> UPPER(someColumn) AND 
      someColumn <> LOWER(someColumn) 
      Collate SQL_Latin1_General_CP1_CS_AS

Upvotes: 9

iamdave
iamdave

Reputation: 12243

I think I understand that you want to find any varchar column with mixed case data within it?

If so, you can achieve this with a cursor looking at your column types, which then executes some dynamic SQL on the varchar columns it finds to check the data for mixed case values.

I thoroughly recommend doing this on a non-production server using a copy of your database, not least because you need to create a table to deposit your findings into:

create table VarcharColumns (TableName nvarchar(max), ColumnName nvarchar(max))

declare @sql nvarchar(max)

declare my_cursor cursor local static read_only forward_only
for 
select 'insert into VarcharColumns select t,c from(select ''' + s.name + '.' + tb.name + ''' t, ''' + c.name + ''' c from ' + s.name + '.' + tb.name + ' where ' + c.name + ' like ''%[abcdefghijklmnopqrstuvwxyz]%'' COLLATE SQL_Latin1_General_CP1_CS_AS and ' + c.name + ' like ''%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'' COLLATE SQL_Latin1_General_CP1_CS_AS having count(1) > 0) a' as s
from sys.columns c
    inner join sys.types t
        on(c.system_type_id = t.system_type_id
            and t.name = 'varchar'
            )
    inner join sys.tables tb
        on(c.object_id = tb.object_id)
    inner join sys.schemas s
        on(tb.schema_id = s.schema_id)

open my_cursor
fetch next from my_cursor into @sql
while @@fetch_status = 0
begin 
    print @sql
    exec(@sql)
    fetch next from my_cursor into @sql
end
close my_cursor
deallocate my_cursor

select * from VarcharColumns

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147344

It sounds like you are after a case sensitive search, so you'd need to use a case sensitive collation for there WHERE clause.

e.g. if your collation is currently SQL_Latin1_General_CP1_CI_AS which is case insensitive, you can write a case sensitive query using:

SELECT SomeColumn
FROM dbo.SomeTable
WHERE SomeField LIKE '%helLo%' COLLATE SQL_Latin1_General_CP1_CS_AS

Here, COLLATE SQL_Latin1_General_CP1_CS_AS tells it to use a case sensitive collation to perform the filtering.

Upvotes: 3

Related Questions