Replace a value in a SELECT statement

Posted on 2012-09-20
Last Modified: 2012-09-21
I am running a SELECT statement and would like to replace a value in the result set.
The value that is being replaced is an integer and the result will be a string.

Here is an example of the statement:

Use xtremeSQL
WHEN [Supervisor ID] = '5' THEN 'John'
ELSE [Supervisor ID]
END AS Supervisor, SSN, Salary
FROM  Employee

Here is the error:

Conversion failed when converting the varchar value 'John' to data type int.

How do I write this?
Question by:mnoisette
    LVL 74

    Accepted Solution

    It sounds like the type of the Supervisor ID column is defined as int. You'll need to cast to keep the types consistent in your CASE:

    WHEN CAST([Supervisor ID] AS varchar(255)) = '5' THEN 'John'
    ELSE CAST([Supervisor ID] AS varchar(255))
    END AS Supervisor, SSN, Salary
    FROM  Employee

    Open in new window

    LVL 18

    Expert Comment

    WHEN [Supervisor ID] = 5 THEN 'John'
    ELSE CAST([Supervisor ID] AS VARCHAR(20))
    END AS Supervisor, SSN, Salary
    FROM  Employee

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now