Post reply

Can someone look over my case function and give me examples?

  • April 15, 2015 at 9:10 am

    #316647

    I have query where i need the second employee column to use the case function to change ms to miss mr to mr and dr to doctor. I tried it and its only returning the last name instead of the title and last name. Can someone suggest why? thanks

    this is what i have plus a picture

    SELECT 'Your full name is ' + CAST(LEN(FirstName) + LEN(LastName) AS VARCHAR(12)) + ' character(s).' AS [Length of Employee Name],

    CASE TitleOfCourtesy WHEN 'Mr' THEN 'Mister ' WHEN 'Ms' THEN 'Miss ' WHEN 'Dr' THEN 'Doctor ' ELSE '' END + LastName AS Title

    FROM dbo.Employees

  • April 15, 2015 at 9:30 am

    #1791123

    I'd guess that you don't have a "Mr", "Ms", "Dr" in the column, instead it probably has white space. Also, if the column doesn't equal one of those options then your case statement is written to output a blank space.

    What does this return?

    SELECT

    SUM(CASE WHEN TitleOfCourtesy = 'Mr' THEN 1 ELSE 0 END) AS [Mr],

    SUM(CASE WHEN TitleOfCourtesy = 'Ms' THEN 1 ELSE 0 END) AS [Ms],

    SUM(CASE WHEN TitleOfCourtesy = 'Dr' THEN 1 ELSE 0 END) AS [Dr]

    FROM dbo.Employees;

    How about this: -

    SELECT 'Your full name is '

    + CAST(LEN(FirstName) + LEN(LastName) AS VARCHAR(12))

    + ' character(s).' AS [Length of Employee Name],

    CASE RTRIM(LTRIM(TitleOfCourtesy))

    WHEN 'Mr' THEN 'Mister '

    WHEN 'Ms' THEN 'Miss '

    WHEN 'Dr' THEN 'Doctor '

    ELSE TitleOfCourtesy

    END + LastName AS Title

    FROM dbo.Employees;

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • April 20, 2015 at 7:53 am

    #1792029

    Thank you I fixed it

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply