CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Shumba Soft
Shumba Soft

Reputation: 97

Convert the first letter of a word to capital using SQL

I am trying to write a query that will make the first letter of the first name a capital and also make the first letter of the last name a capital. I have tried running the query below.

But the output is not correct for example "james plummer" displays as "James Pames"

[FirstName] = UPPER(LEFT(FirstName,1)) + LOWER(SUBSTRING(FirstName,2,LEN(FirstName)))
, [MiddleName]
, [LastName] = UPPER(LEFT(LastName,1)) + LOWER(SUBSTRING(FirstName,2,LEN(LastName)))

Upvotes: 0

Views: 213

Answers (3)

Sean
Sean

Reputation: 827

Thats because in your code, for the [LastName] you do [LastName]=UPPER(LEFT(LastName,1))+LOWER(SUBSTRING(FirstName,2,LEN(LastName)))

When you call SUBSTRING you set FirstName instead of LastName, this causes the LastName to then output as the capitalized first char but ending with the substring of FirstName, which makes a funny combination.

Changing that to: [LastName]=UPPER(LEFT(LastName,1))+LOWER(SUBSTRING(LastName,2,LEN(LastName))) Will fix your problem

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

if you are using sqlserver, you can use stuff function

select [FirstName] = stuff(lower(FirstName), 1, 1, upper(left(FirstName, 1)))
    ,[MiddleName]
    ,[LastName] = stuff(lower(LastName), 1, 1, upper(left(LastName, 1)))

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

seems you have wrong column in lastname +LOWER(SUBSTRING(FirstName,

try change to 

     ,[LastName]=UPPER(LEFT(LastName,1))+LOWER(SUBSTRING(LastName,2,LEN(LastName)))

Upvotes: 0

Related Questions