Link to home
Start Free TrialLog in
Avatar of gogetsome
gogetsomeFlag for United States of America

asked on

update left of hyphen


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
Avatar of knightEknight
knightEknight
Flag of United States of America image


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]) )
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]))
Avatar of gogetsome

ASKER

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?
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
ASKER CERTIFIED SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
thanks.
just tested, my original solution works... just replace for the Company column, as later suggested.
Thank you, I appreciate your time and solution!
It wasn't mine.
... the one accepted.
... or did you not realize this?
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.
Thank you I will make things right.
Thank you, I do appreciate it.