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
gogetsomeAsked:
Who is Participating?
 
knightEknightConnect With a Mentor Commented:
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]) )
0
 
knightEknightCommented:

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

update Vehicles
set [Vehicle]=  'newcompany' + substring([Vehicle], CHARINDEX('-',[Vehicle]), LEN([Vehicle]) )
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
wdosanjosCommented:
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]))
0
 
gogetsomeAuthor 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.
0
 
knightEknightCommented:
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?
0
 
gogetsomeAuthor 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
0
 
knightEknightCommented:
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]) )
0
 
knightEknightCommented:
no strike that, the -1 is not needed.
0
 
knightEknightCommented:
thanks.
0
 
knightEknightCommented:
just tested, my original solution works... just replace for the Company column, as later suggested.
0
 
gogetsomeAuthor Commented:
Thank you, I appreciate your time and solution!
0
 
knightEknightCommented:
It wasn't mine.
0
 
knightEknightCommented:
... the one accepted.
0
 
knightEknightCommented:
... or did you not realize this?
0
 
gogetsomeAuthor Commented:
oh no... I did not realize I screwed the pooch. sorry the points should be yours. How can I fix this?
0
 
knightEknightCommented:
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.
0
 
gogetsomeAuthor Commented:
Thank you I will make things right.
0
 
knightEknightCommented:
Thank you, I do appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.