SQL: Using the like command on a field to set a value in the SQL output

I'm generating an Excel report via SQL but have a limited knowledge.

What I have so far is as follows:

SELECT  table.field "Column1",
        table2.field2 "Column2",
        table3.field3 "Column3",
        table4.field4 "Column4",

from table, table2 etc

where lots of rules

What I need is to add a 5th column to the report based on a field in a table being like one of 5 input strings. I then need to return 1 of 5 output strings which are not in t开发者_运维百科he field.

Example in pseudo code:

if table.field like one return eleven
if table.field like two return twelve
if table.field like three return thirteen
if table.field like four return fourteen
if table.field like five return fifteen

and then I need the returned value to be populated in the 5th column of the report.

If any one can help or point me in the right direction it'd be appreciated. If you've any questions for me please shoot.

Thanks.

Try "SELECT .. CASE"

example:

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

EDIT

To find substrings try CHARINDEX:

USE AdventureWorks2008R2;
GO
SELECT FirstName, LastName, TelephoneNumber, 'NewField' = 
     CASE
          WHEN CHARINDEX('testString1', Address) > 0 THEN 'Value1'
          WHEN CHARINDEX('testString2', Address) > 0 THEN 'Value2'
          WHEN CHARINDEX('testString3', Address) > 0 THEN 'Value4'
          ELSE 'Value4'
     END
FROM Person.vAdditionalContactInfo;

A more specific example would help, but it sounds like you need something like this:

SELECT
  Field1,
  Field2,
  CASE WHEN Field1 = Field2 THEN 'X'
       WHEN Field1 = Field2 THEN 'Y'
       ELSE 'Unrecognized value' END AS CalculatedField
FROM
  Table