Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about CollectivesI need to select columns that end in an uppercase extension.
for example, look at the following table.
id - picture
1 - abc.JPG
2 - def.jpg
3 - 123.jpg
4 - xyz.JPG
the results should give me the rows 1 and 4 because JPG are in uppercase.
can anyone help?
I'm far from an expert, but case sensitivity has hung me up before. Are you able to modify the structure of your table? One thing that may help is changing the collation of the table as follows (SQLFiddle here):
CREATE TABLE pics (id INT, picture VARCHAR(200))
CHARACTER SET latin1 COLLATE latin1_general_cs;
INSERT INTO pics VALUES
(1, 'abc.JPG'),
(2, 'def.jpg'),
(3, '123.jpg'),
(4, 'xyz.JPG')
The _cs stands for case sensitive, and I believe the default is case insensitive, which makes case-based comparisons a bit trickier. You can then use the following query to get your rows:
SELECT *
FROM pics
WHERE picture REGEXP '\.[[:upper:]+]$'
If you do not have access to your underlying table, you could try the following, which casts the column in a different character set (latin1), and then changes the collation to support case-insensitive comparisons (SQLFiddle here):
SELECT *
FROM pics
WHERE CAST(picture AS CHAR CHARACTER SET latin1)
COLLATE latin1_general_cs REGEXP '\.[[:upper:]+]$'
grand.canyon.jpeg? It's possible to write a procedure that will return exactly what you want, but it can go from very simple to very complex quite easily.
\.[A-Z0-9]+$, etc. may work in that case. Could definitely be wrong though :)
Some REGEXPs:
'[.][[:upper:]]+$' -- just uppercase letters
'[.].*[[:upper:]]' -- at least one uppercase letter
'[.].*[[:lower:]]' -- at least one lowercase letter; AND together with previous to get upper and lower, etc.
If there could be two "." in the filename, then consider using SUBSTRING_INDEX(picture, '.', -1) to isolate the 'extension'.
Most SQL languages have a UCASE or UPPER function to convert text to uppercase. I'm also taking advantage of the RIGHT function which isn't in all SQL dialects. If your SQL doesn't have a RIGHT function you'll have to futz with SUBSTRING and LENGTH to get the right three characters in picture.
Select id, picture
from table
where UPPER(RIGHT(TRIM(picture),3)) = RIGHT(TRIM(picture),3)
If the same text converted to uppercase is the same as the unconverted text then it is uppercase in the database and will be selected.
As it can not always be assumed that the file extension will be 3 letters you may use the following to get the first chart after the period and compare it to see if it is uppercase:
select * from table where SUBSTRING(picture,CHARINDEX('.',picture) + 1,1)
= upper(SUBSTRING(picture,CHARINDEX('.',picture) + 1,1)) collate SQL_Latin1_General_CP1_CS_AS
