gogetsome
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
oops, wrong column name:
update Vehicles
set [Vehicle]= 'newcompany' + substring([Vehicle], CHARINDEX('-',[Vehicle]), LEN([Vehicle]) )
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]))
update Vehicles
set Vehicle = company + RIGHT([Vehicle], CHARINDEX('-',[Vehicle] + '-') - LEN([Vehicle]))
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.
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?
ASKER
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
Ford - Ram 1500 STL
I need to updat that to:
Dodge - Ram 1500 SLT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]) )
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.
ASKER
Thank you, I appreciate your time and solution!
It wasn't mine.
... the one accepted.
... or did you not realize this?
ASKER
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.
ASKER
Thank you I will make things right.
Thank you, I do appreciate it.
update Vehicles
set Company = 'newcompany' + substring([Vehicle], CHARINDEX('-',[Vehicle]), LEN([Vehicle]) )