Set Values in column B based on values in Column A in SQL2005?

I'm trying to take an existing column and parse each row for certain words in a string, i.e. Sheet, Page, Card, and based on that word (only one instance of one of these words is in a row) populate a new column in the same table with a value. If it did not find any of those words, leave Column B blank.

I.E. Column A contains the word "Sheet", populate Column B with the letter "S"

So the table would be something like:

Column A  Column B
Sheet        S
Page         开发者_StackOverflowP
Card         C

Any help would be appreciated!

UPDATE YourTable
    SET ColumnB = CASE WHEN ColumnA LIKE '%Sheet%' THEN 'S'
                       WHEN ColumnA LIKE '%Page%' THEN 'P'
                       WHEN ColumnA LIKE '%Card%' THEN 'C'
                       ELSE NULL /* or '' if you prefer */
                  END

;WITH mappings (ColumnA, ColumnB) AS
(
 SELECT 'Sheet','S' UNION ALL
 SELECT 'Page','P' UNION ALL
 SELECT 'Card','C' 
)
UPDATE y
SET y.ColumnB= m.ColumnB
FROM YourTable y
JOIN mappings m ON y.ColumnA LIKE '%' + m.ColumnA + '%'

update myTable 
set columnB = substring(columnA, 1, 1)