Solved

update left of hyphen

Posted on 2011-09-06
20
301 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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
 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 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