CODEWITHSUNDEEP
sqloracle-database
Alex V
Alex V

Reputation: 81

SQL capitalize first and last letter of a name

I want to make a query that is showing me last_name with first letter capitalized and also the last one capitalized.

Example: last_name='Alexander' after query: last_name='AlexandeR'

I tried like this

select initcap(last_name)+LOWER(SUBSTR(last_name,2,LENGTH(last_name))) name_last from employees

but I am getting this error 01722. 00000 - "invalid number"

I am using SQL Oracle DB

Can someone explain me what is the problem?

Upvotes: 3

Views: 9393

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The problem is the concatenation character. However, I want to suggest a slightly different approach:

select (upper(substr(last_name, 1, 1)) ||
        substr(last_name, 2, len(last_name) - 2) ||
        upper(substr(last_name, -1, 1))
       )

In other words, avoid initcap(). The issue is unexpected side-effects. initcap() capitalizes the first letter of every word in the string. So, if the name consisted of multiple words, then each would be capitalized. Under such circumstances, I don't think it is wise to assume that the last name consists of only one name.

Upvotes: 7

Alok Gupta
Alok Gupta

Reputation: 1360

To concatenate the string you are using '+' which is considering your string are number which is not the case and you are getting this error.

To concatenate strings in Oracle, please use either || operator or CONCAT() function.

So your query becomes like:

select initcap(last_name) || LOWER(SUBSTR(last_name,2,LENGTH(last_name))) name_last from employees;

Now you can modify this to get the output you are expecting.

Upvotes: 1

Related Questions