PostgreSQL change part of a string to uppercase

Question

I have a field named rspec in a table trace.

So for now the field is like "Vol3/data/20070204_191426_FXBS.v3a".

All I need is a query to change it to the format "Vol3/data/20070204_191426_FXBS.V3A".

Do you only want to make the "extension" uppercase, or anything behind the second /? — user330315
– user330315, Commented Feb 15, 2012 at 23:02

user330315 · Accepted Answer · 2012-02-15 23:14:29Z

2

Assuming the current version:

select left(rspec, - 3)||upper(right(rspec, 3))
from trace

For older versions:

select substr(rspec, 1, length(rspec) - 3)||upper(substring(rspec from '...$'))
from trace

answered Feb 15, 2012 at 23:14

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Erwin Brandstetter Over a year ago

+ 1 elegant use of the new left() with a negative length.

Erwin Brandstetter · Accepted Answer · 2012-02-16 00:51:44Z

2

Or, to cover all possibilities like

file extensions of variable length: abc123.jpeg
no file extension at all: abc123
dot as last character: abc123.
multiple dots: abc.123.jpg

SELECT CASE WHEN rspec  ~~ '%.%'
          THEN substring(rspec, E'^.*\\.')
            || upper(substring(rspec , E'([^.]*)$'))
          ELSE rspec
       END AS rspec 
FROM  (VALUES
         ('abc123.jpeg')
       , ('abc123')
       , ('abc123.')
       , ('abc.123.jpg')
         )  ASx(rspec); -- testcases

Explain:

If the string has no dot, use the string.
Else, take everything up to and including the last dot in the string.
Append everything after the last dot in upper case.

edited Feb 16, 2012 at 0:51

answered Feb 16, 2012 at 0:43

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

PostgreSQL change part of a string to uppercase

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related