?
Solved

update left of hyphen

Posted on 2011-09-06
20
Medium Priority
?
328 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
19 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.
Suggested Courses

749 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