Reputation: 1
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
Reputation: 1271241
There are various methods. One is:
UPDATE Supplier
SET SupplierName = UPPER(LEFT(SupplierName, 1)) + LOWER(STUFF(SupplierName, 1, 1, ''));
Upvotes: 0
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