Extracting year from string - Urgent!

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

SQL Server 2005 Forums

Transact-SQL (2005)

Extracting year from string - Urgent!

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-08 : 23:52:54

Hi,
I have a column where year is stored.
the column is varchar and the data is of diff types:
like 'Feb 2008' 2003-2005. May-2009 etc.

of these i must select only the year part....

i thought of using like but how will i know what way the data is stored?
can you tell a generic query for this?

Thanks in advance...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-08 : 23:56:00

there isn't a generic query it depends on the how your data is

for 'Feb 2008', 'May-2009' you can use right(col, 4) to extract the year

for '2003-2005' which date do you want ? use left() or right() to extract it

KH
[spoiler]Time is always against us[/spoiler]

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-09 : 00:02:44

khtan,
thanks a lot for your quick response.
right now i am writing the query in the same way you mentioned.
but there is one like cond with [A-Z] something like this.... m not sure of this.
will this help in anyway?.....
if not , then i will go with your reply...
thanks again...

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-09 : 00:11:11

my data is like this:
2007
5/2003-1/2005
November 2001 – Present
Jan 2008 - Present
2003-2004 -- (in this case i will have to split the data into two columns of the table : from and to year cols.)

could you pls suggest something?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-09 : 00:18:08

quote:
Originally posted by mrm23

my data is like this:
2007
5/2003-1/2005
November 2001 – Present
Jan 2008 - Present
2003-2004 -- (in this case i will have to split the data into two columns of the table : from and to year cols.)

could you pls suggest something?

are these all the possible format or you have others ?

KH
[spoiler]Time is always against us[/spoiler]

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-09 : 00:21:57

this is actually a migration process... there are thousands of records....
most of them are like this.... i am not sure what other possibiliites are there....
when i made a distinct, i found 208 diff rows :( now how can i move them all to my db with the right format?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-09 : 00:45:20

you will probably need to fine tune this further depending on your data

try


select	data,
	fr_year	= right(rtrim(left(data, charindex('-', data + '-') - 1)), 4),
	to_year	= case 	when data like '%-%'
			then 
				case when right(data, len(data) - charindex('-', data)) like '%[1-2][0-9][0-9][0-9]'
				then right(data, 4)
				end
			end
from
(
	select	data = '2007'				union all
	select	data = '5/2003-1/2005'			union all
	select	data = 'November 2001 - Present'	union all
	select	data = 'Jan 2008 - Present'		union all
	select	data = '2003-2004'
) d

KH
[spoiler]Time is always against us[/spoiler]

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-09 : 01:07:06

thanks a lot khtan! that works fantastically..... :)
but i am still not done with this much ....
in the remote db, the year is stored in a single column, and the data is as i mentioned.
but in our db, the way the dates are stored is entirely diff.
we have a FromYear- ToYear, FromMonth-ToMonth and FromDate-ToDate Columns.
so that single coulmn in the client db is to be splitted to fill all these 6 columns...

And the big question is , this is not the only insertion i am making. i am inserting into two tables.
there are many other columns to be inserted. i have to merge this query with the code i have written to insert those columns....

There i am fetching rows one by one and filling them to master and detail accordingly....

Suggest me if you have any idea of doing it in one shot...
I will try to do this first... let me see...

Thanks a lot!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-09 : 01:22:10

quote:
we have a FromYear- ToYear, FromMonth-ToMonth and FromDate-ToDate Columns.

looking at the sample you have posted, most of these information are not available. Only the month is available. You will still surely required to do some further string parsing to extract the required information.

KH
[spoiler]Time is always against us[/spoiler]

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-09 : 01:49:34

can i write a function which returns all these values and then make insert as
select (a,b,c,function.col)?

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-09 : 04:52:44

Hi,
I have tried to write a SP here for the above calculation.
but this is working only for the first row of the table.

What may be wrong ? and how can i now use the values returned from this in my insert statements?

please help...

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-09 : 04:53:08

This is the SP:

alter PROC sp_SplitYear
@CUST_Year VARCHAR(100),
@FromYear VARCHAR(100) OUTPUT,
@ToYear VARCHAR(100) OUTPUT
AS
BEGIN
select @CUST_Year = CUST_MovedYear1_102459906
FROM TP.dbo.TBL_CONTACT WHERE CUST_MovedYear1_102459906 is not null
print 'Custyear = ' + @CUST_Year

SET @FromYear = RIGHT(RTRIM(LEFT(@CUST_Year, CHARINDEX('-', @CUST_Year + '-') - 1)), 4)

select @ToYear = CASE WHEN @CUST_Year LIKE '%-%'
THEN
CASE WHEN RIGHT(@CUST_Year, LEN(@CUST_Year) - CHARINDEX('-', @CUST_Year)) LIKE '%[1-2][0-9][0-9][0-9]'
THEN RIGHT(@CUST_Year, 4)
END
END
from
(select CUST_MovedYear1_102459906 from tp.dbo.tbl_contact
where category is null and CUST_MovedYear1_102459906 is not null)A
-- IF @@ROWCOUNT = 1
-- RETURN 1
print @FromYear
print @ToYear
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-09 : 05:32:26

just use the below query, replace data with your actual column name


select	fr_year	= right(rtrim(left(data, charindex('-', data + '-') - 1)), 4),
	to_year	= case 	when data like '%-%'
			then 
				case when right(data, len(data) - charindex('-', data)) like '%[1-2][0-9][0-9][0-9]'
				then right(data, 4)
				end
			end
from   <your table>

KH
[spoiler]Time is always against us[/spoiler]

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-10 : 01:26:47

hi khtan , thanks a lot.... i could do it....
but i am not sure how charindex works.... could you please explain?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-10 : 02:16:43

quote:
Originally posted by mrm23

hi khtan , thanks a lot.... i could do it....
but i am not sure how charindex works.... could you please explain?

Please refer to the Books Online. Detail explanation with examples there
CHARINDEX (Transact-SQL)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources