Separating out year from column

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Separating out year from column

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-26 : 15:08:45

Pre-Owned 1996 Winnebago Vectra Motorhome
I have a column with entries like so,

Pre-Owned 1991 Winnebago Unknown Motorhome
New 2007 Winnebago Voyage Motorhome
Pre-Owned 2005 Winnebago Voyage Motorhome
New 2009 Winnebago Aspect Motorhome
Outlook
Sightseer
Vista
Destination
Tour
Era
Class C Winnebago Outlook 31C
Class C Winnebago Outlook 329B
Class A Winnebago Sightseer 30B

is it possible to filter out only the year and put that into another column?

for example the first 4 entries would filter out 1991, 2007, 2005 and 2009 and put it in another column.

the rest would do nothing because theres no year.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-08-26 : 15:22:26

[code]
create table #tmp (c1 varchar(255))
insert into #tmp
select 'Pre-Owned 1991 Winnebago Unknown Motorhome' union
select 'New 2007 Winnebago Voyage Motorhome' union
select 'Pre-Owned 2005 Winnebago Voyage Motorhome' union
select 'New 2009 Winnebago Aspect Motorhome'

select
c1,
substring(c1, PATINDEX ('%[1,2][0-9][0-9][0-9]%', c1), 4) as MyYear
from #tmp

drop table #tmp
[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-27 : 02:36:21

quote:
Originally posted by jhocutt

create table #tmp (c1 varchar(255))
insert into #tmp
select 'Pre-Owned 1991 Winnebago Unknown Motorhome' union
select 'New 2007 Winnebago Voyage Motorhome' union
select 'Pre-Owned 2005 Winnebago Voyage Motorhome' union
select 'New 2009 Winnebago Aspect Motorhome'

select
c1,
substring(c1, PATINDEX ('%[1,2][0-9][0-9][0-9]%', c1), 4) as MyYear
from #tmp

drop table #tmp

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

To work in all data,

select 
	c1,
	case when PATINDEX ('%[1,2][0-9][0-9][0-9]%', c1)>0 then substring(c1, PATINDEX ('%[1,2][0-9][0-9][0-9]%', c1), 4) 
		else null end as MyYear
from #tmp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources