So I'm trying to format a column in a stored procedure, if the value is " boxter s" I want it to be retrieved as "Boxter S" I've sorted the 1st capital letter but I'm having trouble using the CASE statement to make the last letter a capital letter a capital if there is a space in the value. Not all values have a space hence the CASE statement and if there is a space there will only be one with one letter preceding it. Here is what I have so far:
SELECT CASE UPPER(LEFT(m.model, 1)) + LOWER(SUBSTRING(m.model, 2, LEN(m.manufacturer))) WHEN m.model = % + ' ' THEN UPPER(RIGHT(m.model, 1))END AS Model
You want something like this:
select case when substring(model, len(model) - 2, 1) = ' ' then formatted else model end model
Work on the case logic and the formatting separately.