CODEWITHSUNDEEP
sqlsql-server
user5021612
user5021612

Reputation:

How to replace a text in a column?

I have a column which looks like

Quarter
-------
Q1 2012
Q2 2012
Q1 2013

and I want

Quarter
-------
Q1
Q2
Q1

Here's what I've come with:

UPDATE TABLE [WA Products Sales].[dbo].[WA_Sales_Products]
SET 
    Quarter = REPLACE (Quarter, SUBSTRING(Quarter, 1, CHARINDEX(' ', Quarter)-1)

Which returns

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'TABLE'.

Any help would be appreciated.

Upvotes: 1

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The problem with this query:

UPDATE TABLE [WA Products Sales].[dbo].[WA_Sales_Products]
    SET Quarter = REPLACE(Quarter, SUBSTRING(Quarter, 1, CHARINDEX(' ', Quarter)-1)

is that it fails when Quarter does not have a space. A more minor problem is that LEFT() is more appropriate. You can fix the first problem by adding in a space:

UPDATE TABLE [WA Products Sales].[dbo].[WA_Sales_Products]
    SET Quarter = LEFT(Quarter, CHARINDEX(' ', Quarter + ' ') - 1);

This should fix the issue with a bad argument to LEFT().

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

Unless I'm missing something, this should get you the desired results:

UPDATE [WA Products Sales].[dbo].[WA_Sales_Products]
SQL Quarter = LEFT(Quarter, 2)
WHERE LEN(Quarter) > 1 -- added this condition

Upvotes: 0

Related Questions