Find All Records With A Lowercase Letter
-
OK, here is one that has me stumped!
I have a table that has a column of alpha-numeric reference codes. I want to perform a select (and eventally, a delete) statement to find all records where this column contains a lowercase letter in the value.
For Example:
1 | ABC54321
2 | abc48d56
3 | 541ad885
4 | 74AD56A
5 | AbCd881
I want to perform a search that would return only rows 2,3 and 5 in the example above. Just where a lowercase a-z is present.
This will be a one time query/delete and I can not make any changes to the column properties including the Collation type.
I would appreciate any assistance you guys can offer.
Bob Gibilaro
-
First of all what is the colation set for the field on that table?
Default is SQL_Latin1_General_Cp1_CI_AS
Meaning Latin, General, CI = Case insensitive, AS = Ascending Sort If this is the case then.
This will work, probly not very fast though, but that was not a requirement.
Declare @a table (pk int identity, SomeCode varchar(10))
insert into @a (SomeCode)
values('ABC54321')
insert into @a (SomeCode)
values('abc48d56')
insert into @a (SomeCode)
values('541ad885')
insert into @a (SomeCode)
values('74AD56A')
insert into @a (SomeCode)
values('AbCd881')
select *
from @a
where somecode like '%[abcdefghijklmnopqrstuvwxyz]%' COLLATE SQL_Latin1_General_CP1_CS_AS
Great article about Case sensativity.
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm
If it is CS, then you just compare on the lower case letter.
where Somecode like '%[abcdefghijklmnopqrstuvwxyz]%'
Because case sensative collation manages case sensativity.
-
I am ashamed at how easy this was!
I did not even have to use the table variable, like you suggested. I just queried against the live table and used the WHERE clause you provided. Worked like a charm!
Thanks so much. It made my day.
Bob Gibilaro
-
The table variable was simply for demo purpuses
. -
I created a table variable and inserted some records so I could run the query to test for the correct results
-
You can also use
where somecode != upper(somecode) COLLATE SQL_Latin1_General_CP1_CS_AS
-
One comment: in the LIKE clause, you could use '%[a-z]%' instead of typing the whole alphabet. Not sure how that would change for non-english alphabets.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
I tried that before proposing upper(). I tried it using an english alphabet ... and it does not work with '%[a-z]%'. I do not understand why ...
Bert
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply