Jonathan Clark Jonathan Clark - 1 year ago 151
SQL Question

sql server SELECT CASE

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download