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 |
|
HenryFulmer
Posting Yak Master
110 Posts |
 Posted - 2014-07-14 : 15:21:43
|
| I have a column containing year range info.The valuses look like:1998-19981999-2008Using those examples I would like to change the records to 9898-08Thanks for any assistance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-14 : 15:32:52
|
| [code]d eclare @s varchar(20)--set @s = '1998-1998'set @s = '1999-2008'select case when left(@s, 4) = right(@s, 4) then right(@s, 2) else substring(@s, 3, 3) + right(@s, 2) end[/code]Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-07-14 : 15:59:48
|
| Stripping century seems like a very bad idea. At least it's clear at a glance what the current data is.Then again, you really should have a separate column for each value anyway. |
|
|
|
HenryFulmer
Posting Yak Master
110 Posts |
Posted - 2014-07-14 : 16:43:56
|
| @tkizer Thanks - this works great!@ScottPletcher - you're correct each year value should be -and is- in addition to it's consolidated value in its own column. However, for the purpose of this particular result set I need to conserve as much space as possible, hence the attempt to consolidate and shrink. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-07-14 : 18:42:57
|
| Ok, that makes sense.Sounds like you'll be UPDATEing the data. If so, I'd put a check in to make sure you don't update the same row twice and destroy the data:update ...set ... = case when left(combined_date_column, 4) = right(combined_date_column, 4) then right(combined_date_column, 2) else substring(combined_date_column, 3, 3) + right(combined_date_column, 2) endwhere len(combined_date_column) >= 9 |
|
|
|
|
|
|
|
|
string manipulation