[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

update left of hyphen

Posted on 2011-09-06
20
Medium Priority
?
324 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Independent Software Vendors: 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. …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

650 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