How to replace a text in a column?

Question

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.

there's definetly missing a closing bracket, but if this is the problem... — Turo
– Turo, Commented Feb 18, 2017 at 9:39
Nope, I just didn't select it while copying the syntax. But thanks. — user5021612
– user5021612, Commented Feb 18, 2017 at 9:41
MS is not my dialect... are you sure the TABLE keyword is needed? Are the brackets [] correct? — Pinke Helga
– Pinke Helga, Commented Feb 18, 2017 at 9:45
Replace takes 3 Arguments, why not simply Quarter = SUBSTRING(Quarter, 1, CHARINDEX(' ', Quarter)-1) — Turo
– Turo, Commented Feb 18, 2017 at 9:45
It works without TABLE. Quarter = SUBSTRING(Quarter, 1, CHARINDEX(' ', Quarter)-1) produces Msg 537, Level 16, State 2, Line 6 Invalid length parameter passed to the LEFT or SUBSTRING function. — user5021612
– user5021612, Commented Feb 18, 2017 at 9:48

Gordon Linoff · Accepted Answer · 2017-02-18 13:19:15Z

1

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().

answered Feb 18, 2017 at 13:19

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zohar Peled · Accepted Answer · 2017-02-18 10:02:16Z

0

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

edited Feb 18, 2017 at 10:02

answered Feb 18, 2017 at 9:53

Zohar Peled

82.9k10 gold badges79 silver badges134 bronze badges

6 Comments

user5021612 Over a year ago

Invalid length parameter passed to the LEFT or SUBSTRING function. But I wanted to make it more universal.

user5021612 Over a year ago

It's odd, because this works fine: ALTER TABLE [WA Products Sales].[dbo].[WA_Sales_Products] ADD Q AS LEFT (Quarter, charindex(' ', Quarter)-1)

Zohar Peled Over a year ago

See my edited answer. Added a where condition so that the update will only take place on values longer then one char. This should prevent the error you reported.

user5021612 Over a year ago

Invalid length parameter passed to the LEFT or SUBSTRING function. This is beginning to be a kind of frustrating...

user5021612 Over a year ago

Thanks, it seems the syntax you provided is correct. I'm getting the same error whatever syntax I write, even SELECT TOP(100) * FROM ... produce Invalid length parameter passed to the LEFT or SUBSTRING function. now. It looks like some problem in Management Studio, but it persist even after restart.

|

Collectives™ on Stack Overflow

How to replace a text in a column?

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related