Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2003-03-11 : 13:29:04
|
| OK, before I even ask the question, let me point out that I know this is only coming up because of a badly designed table, but that is the problem I'm faced with now.I have a column in the table called segmentDesc. The value in this column take the form of segmentDate-segmentSequence. Some example values would be 12/04/03-100, 12/04/03-200, 12/04/03-1000...Here is the problem I have. I need to update the columns so the segmentDate portion of the segmentDesc gets changed, but the segmentSequence stays the same.I would want my new example data to look something like 01/01/04-100, 01/01/04-200, 01/01/01-1000. Is there a way to do this with the UPDATE statment, or is it going to get a lot more complicated because of the string parsing and whatnot? Thanks-Nick |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-11 : 13:37:52
|
| Something like :Update Tableset field = '01/01/04' + substring(field,9)Are you updating all fields to a particular date? do you need to join to another table for the dates or use a formula?- Jeff |
 |
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2003-03-11 : 13:45:32
|
| No, something like that would work. Is there a way to split the string at the '-' instead of wiring the length of the date. I could see problems arrising with the difference in 01/01/04 and 01/01/2004. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-11 : 14:16:11
|
| How About:UPDATE TABLESET segmentDesc = @NewDate + SUBSTRING(segmentDesc , CHARINDEX(segmentDesc,'-',1) , Len(segmentDesc)-CHARINDEX(segmentDesc,'-',1))WHERE @OldDate = SUBSTRING(segmentDesc , 1 , CHARINDEX(segmentDesc,'-',1)-1)Brett8-) |
|
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-03-12 : 01:38:09
|
| We can use REPLACE function also.update test set col1 = replace(col1, substring(col1,1, charindex('-', col1, 1)-1), getdate())___________________________________________________________________________Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
|
|
|
|
|
|
UPDATE column