CODEWITHSUNDEEP
sqlsql-server
Gojiranotron
Gojiranotron

Reputation: 1

Updating everything after the first character in SQL to be lowercase?

I have a table full of suppliers names, and the front of the website pulls from this list. They have been entered in over a long period of time and have a mix of all caps or just the first character capitalised.

How do I just update for the second character onwards ?

UPDATE Supplier SET SupplierName = LOWER(SupplierName)

This will obviously change every character to be lower, but this will create more work than just manually editing the capitalised names. I'm not sure how to work in 'LEFT' with this update command to pick the second character onwards.

For more detail, it's running on MSQL Server management studio and the db itself is sqlexpress2014.

Upvotes: 0

Views: 981

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

There are various methods. One is:

UPDATE Supplier
    SET SupplierName = UPPER(LEFT(SupplierName, 1)) + LOWER(STUFF(SupplierName, 1, 1, ''));

Upvotes: 0

TimLer
TimLer

Reputation: 1360

This query can solve your problem in oracle database:

UPDATE Supplier SET SupplierName =  UPPER(SUBSTR(SupplierName ,1,1)) || lower(SUBSTR(SupplierName ,-LENGTH(SupplierName )+1))

With SUBSTR(SupplierName ,1,1) function you can get the first character of your String.

With SUBSTR(SupplierName ,-LENGTH(SupplierName )+1)) you will get every character after first. And if you put it in lower() you can make every character lowercase.

If you use MSSQL, you can use this query:

update Supplier SET SupplierName = UPPER(SUBSTRING(SupplierName,1,1)) + lower(SUBSTRING(SupplierName ,2,LEN(SupplierName )))

Upvotes: 1

Related Questions