Conditionally editing the output in a SQL query

Question

I'm trying to pull data from a column in a table I have called Vendors and the column name is VendorContactLName. I also have a column VendorContactFName. I'm trying to make it so as I pull the data, it conditionally changes the output of the string.

For example, I want to add an apostrophe s <'s> to the end of the name which I can do with:

SELECT VendorContactFName + ' ' + VendorContactLName + '''s' AS 'Vendor Full Name' FROM Vendors;

However, I'd like to make it so that if it happens to be that the last name in VendorContactLName already ends with an "s" that I only concatenate an apostrophe to it, not apostrophe and an 's'.

So, I'd like: Karl Allen, Michael Dunlap, and Michelle Higgins to come out as Karl Allen's, Michael Dunlap's, and Michelle Higgins'.

Thanks for the help.

Answer 1

Check this:

SELECT VendorContactFName + ' ' +
 (case right(VendorContactLName,1) 
  when 's' then VendorContactLName + ''''
  else VendorContactLName + '''s' 
  end) as 'Vendor Full Name'
 FROM Vendors

demo here