?
Solved

update left of hyphen

Posted on 2011-09-06
20
Medium Priority
?
321 Views
Last Modified: 2012-05-12

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
0
Comment
Question by:gogetsome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 5
20 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491046

update Vehicles
set Company =  'newcompany' + substring([Vehicle], CHARINDEX('-',[Vehicle]), LEN([Vehicle]) )
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491053
oops, wrong column name:

update Vehicles
set [Vehicle]=  'newcompany' + substring([Vehicle], CHARINDEX('-',[Vehicle]), LEN([Vehicle]) )
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 36491064
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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 

Author Comment

by:gogetsome
ID: 36491149
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491172
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
 

Author Comment

by:gogetsome
ID: 36491195
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
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 36491224
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491265
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491281
no strike that, the -1 is not needed.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491285
thanks.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491292
just tested, my original solution works... just replace for the Company column, as later suggested.
0
 

Author Comment

by:gogetsome
ID: 36491321
Thank you, I appreciate your time and solution!
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491339
It wasn't mine.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491343
... the one accepted.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491360
... or did you not realize this?
0
 

Author Comment

by:gogetsome
ID: 36491516
oh no... I did not realize I screwed the pooch. sorry the points should be yours. How can I fix this?
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491621
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
 

Author Comment

by:gogetsome
ID: 36491634
Thank you I will make things right.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36491638
Thank you, I do appreciate it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question