Update another column if text in another exists 2

[Cpreston]

Hi

I have a column that lists a product description. I want to populate a new column if a certain word exists in the description.

Si if like 'red' in the description I want the Wood Type column to be Red Wood. If Like White then White Wood. Else Other. I cannot seem to find an example anywhere on line, any ideas please. Thanks

I cannot figure out how to do this in my query. The column the description is in is dbo.[148-vwWOFinishedProducts].[Source Description]

SELECT        dbo.[148-vwWOFinishedProducts].WorksOrderNumber, dbo.[148-vwWOFinishedProducts].ProductCode, dbo.[148-vwWOFinishedProducts].Description, 
                         dbo.[148-vwWOFinishedProducts].ProductID, dbo.[148-vwWOFinishedProducts].[Source Product], dbo.[148-vwWOFinishedProducts].[Source Description], dbo.[148-vwWOFinishedProducts].Thickness, 
                         dbo.[148-vwWOFinishedProducts].Width, dbo.WorksOrderSchedule.ScheduleNumber
FROM            dbo.WorksOrderScheduleLine INNER JOIN
                         dbo.WorksOrderSchedule ON dbo.WorksOrderScheduleLine.WorksOrderScheduleID = dbo.WorksOrderSchedule.WorksOrderScheduleID INNER JOIN
                         dbo.[148-vwWOFinishedProducts] ON dbo.WorksOrderScheduleLine.WorksOrderID = dbo.[148-vwWOFinishedProducts].WorksOrderID
WHERE        (dbo.WorksOrderSchedule.ScheduleNumber = 27624)
ORDER BY dbo.[148-vwWOFinishedProducts].WorksOrderNumber

 

[SaltyTheFrog]

Can you put in a case statement. Like:

Case PieceOfWood
When like '%white%' then 'White Woood'
When like '%red%' then 'Red Woood'
else 'Other' End as 'NewCoumn'

 

[Andrzejek]

You may want to have another small table like:

[pre]
NewTable
SHORT FULL
RED Red Wood
WHITE White Wood
....
[/pre]
And then use it in your Select:
[tt]
Select P.[Source Description], N.FULL
From dbo.[148-vwWOFinishedProducts] P, NewTable N
Where UPPER(P.[Source Description]) [blue]LIKE ('%'[/blue] || N.SHORT || [blue]'%'[/blue])
[/tt]
Assuming your NewTable returns one record, because if your [tt]Source Description[/tt] contains "Some red and white pieces", you will have problems.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Salty, your version would be:

[pre]
CASE WHEN dbo.[148-vwWOFinishedProducts].[Source Description] like '%red%' then 'Red Woood'
WHEN dbo.[148-vwWOFinishedProducts].[Source Description] like '%white%' then 'White Woood'
END AS NEW_COLUMN
[/pre]
but that would mean all cases would have to be included in the SELECT statement :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

Thanks guys all working now. I used the CASE way only because of the ad hoc way people created descriptions. But both good options Thanks again