update left of hyphen

gogetsome
gogetsome used Ask the Experts™
on

Hello, I need some help with the following statement. I need to update the vehicle column to correct the company name that proceeds the hypen in that column.

Why doesn't this statement work?

update Vehicles
set (LEFT([Vehicle], CHARINDEX('-',[Vehicle] + '-') - 1)) = company
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

update Vehicles
set Company =  'newcompany' + substring([Vehicle], CHARINDEX('-',[Vehicle]), LEN([Vehicle]) )
oops, wrong column name:

update Vehicles
set [Vehicle]=  'newcompany' + substring([Vehicle], CHARINDEX('-',[Vehicle]), LEN([Vehicle]) )
Top Expert 2011

Commented:
SET requires a column name on the left side of the equal sign not an expression.  The update should be something like this:

update Vehicles
set Vehicle = company + RIGHT([Vehicle], CHARINDEX('-',[Vehicle] + '-') - LEN([Vehicle]))
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
knightEknight your statement just concatinated the company name and the company name like this fordford

wdosanjos yours throws this error:
Msg 536, Level 16, State 2, Line 2
Invalid length parameter passed to the RIGHT function.
The statement has been terminated.
Do I understand your problem correctly - you want to replace the part of the string before the hyphen with a new company name, and leave the part of the string after the hyphen the way it was, correct?

Author

Commented:
Yes that is correct. The new company name is in the company column. For example the vehicle column looks like this:

Ford - Ram 1500 STL


I need to updat that to:


Dodge - Ram 1500 SLT
Before you run the update, run this SELECT so we can see what the result will be:

select [Vehicle], [Company], [Company] + substring([Vehicle], CHARINDEX('-',[Vehicle]), LEN([Vehicle]) )
from Vehicles

I believe my previous code should do the trick using the Company column name, so I'm not sure why you are seeing that result.  Hopefully the SELECT above will help clarify this.  Let me know how it looks...

update Vehicles
set [Vehicle] =  Company + substring( [Vehicle], CHARINDEX('-',[Vehicle]), LEN([Vehicle]) )
actually we may need to subtract 1 from the charindex result, but this may cause unexpected results if there is no hyphen:


select [Vehicle], [Company], [Company] + substring( [Vehicle], CHARINDEX('-',[Vehicle])-1, LEN([Vehicle]) )
from Vehicles

update Vehicles
set [Vehicle] =  Company + substring( [Vehicle], CHARINDEX('-',[Vehicle])-1, LEN([Vehicle]) )
no strike that, the -1 is not needed.
just tested, my original solution works... just replace for the Company column, as later suggested.

Author

Commented:
Thank you, I appreciate your time and solution!
It wasn't mine.
... the one accepted.
... or did you not realize this?

Author

Commented:
oh no... I did not realize I screwed the pooch. sorry the points should be yours. How can I fix this?
A split is appropriate, since both solutions are basically the same (RIGHT is a form of SUBSTRING).  I'll have the question re-opened if you don't mind, then you can split them as you see fit.

Author

Commented:
Thank you I will make things right.
Thank you, I do appreciate it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial