63

How do you make a field in a sql select statement all upper or lower case?

Example:

select firstname from Person

How do I make firstname always return upper case and likewise always return lower case?

ErikE
  • 48,881
  • 23
  • 151
  • 196
Joshua Hudson
  • 2,187
  • 2
  • 20
  • 24
  • What happens when you move to a database (or other repository - XML?) that does not support case changing? Consider doing this in code or performing case-insensitive operations. – Anthony Mastrean Dec 04 '08 at 17:15
  • Thanks for the comment ajmastrean. I would fully agree with your statement and I rarely do any casing in SQL, which is why I forget how to do this from time to time when I do have a need it. – Joshua Hudson Dec 04 '08 at 17:26

5 Answers5

101
SELECT UPPER(firstname) FROM Person

SELECT LOWER(firstname) FROM Person
Jared
  • 8,390
  • 5
  • 38
  • 43
16

LCASE or UCASE respectively.

Example:

SELECT UCASE(MyColumn) AS Upper, LCASE(MyColumn) AS Lower
FROM MyTable
Stephen Wrighton
  • 36,783
  • 6
  • 67
  • 86
  • 1
    UCASE for lower and LCase for Upper? – Joshua Hudson Dec 04 '08 at 17:10
  • @Joshua - LCASE & UCASE are inherent within the SQL language itself not related to any specific implementation of SQL within a RDBMS. And I fixed the UCASE/LCASE for Lower/Upper thing. – Stephen Wrighton Dec 04 '08 at 17:12
  • Thank you. I gave you a up vote because you are right about ISO SQL vs an implementation. – Joshua Hudson Dec 04 '08 at 17:22
  • 3
    `LCASE/UCASE` seem not to be known in Oracle (11.2.0.4.0), while `LOWER/UPPER` work – Andre Holzner Jun 06 '14 at 08:23
  • LCASE does not exist in Postgres! Not sure if oracle and postgres are just out of spec, but this cannot be depended on for cross-database functionality. like Andre said, use LOWER/UPPER. They're supported on MySQL, MsSql and Postgres – Alkanshel Sep 30 '15 at 23:48
5

SQL SERVER 2005:

print upper('hello');
print lower('HELLO');
Cirieno
  • 481
  • 2
  • 8
  • Perfect. Thank you Cirieno! I actually had this answer but wanted it to be documented on stack overflow since I only use this rarely I always forget it. :) – Joshua Hudson Dec 04 '08 at 17:07
2

You can use LOWER function and UPPER function. Like

SELECT LOWER('THIS IS TEST STRING')

Result:

this is test string

And

SELECT UPPER('this is test string')

result:

THIS IS TEST STRING
Muhammad Awais
  • 4,238
  • 1
  • 42
  • 37
0

You can do:

SELECT lower(FIRST NAME) ABC
FROM PERSON

NOTE: ABC is used if you want to change the name of the column

xgord
  • 4,606
  • 6
  • 30
  • 51
Xyed Xain Haider
  • 488
  • 4
  • 16