Solved

update left of hyphen

Posted on 2011-09-06
20
290 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
  • 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
 

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 500 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now