March 10, 2010 at 11:57 pm
Dear Experts ,
I have the following string
SBR9D28.0178
The fourth character from the left stands for year i.e. 2009
The fifth character from the left stands for the month i.e. April
I would like to create a query that would parse through the string checking the number whether 9 for 2009 or 11 for 2011. Then append the '20' or '200' to create a year.
Help me with this
March 11, 2010 at 1:35 am
Hi Martin,
If you have the value as a string in a variable, this should do it:
declare @date varchar(20)
set @date = 'SBR9D28.0178'
--set @date = 'SBR11L28.0178'
declare @year int
declare @month int
declare @yearlength tinyint
-- Check if the fifth character is a digit - in that case the year has two digits
set @yearlength = case when substring(@date, 5, 1) like '[0-9]' then 2 else 1 end
set @year = 2000 + cast(substring(@date, 4, @yearlength ) as int)
set @month = ascii(substring(@date, 4 + @yearlength, 1)) - 64 -- 'A' is ascii 65
select @year as [Year], @month as [Month]
If the string is stored in a column and you want to get year and month from all rows, it is possible to rewrite the code to a SET-based version.
Hope it works as planned!
/Markus
March 11, 2010 at 1:40 am
martin.edward (3/10/2010)
Dear Experts ,I have the following string
SBR9D28.0178
The fourth character from the left stands for year i.e. 2009
The fifth character from the left stands for the month i.e. April
I would like to create a query that would parse through the string checking the number whether 9 for 2009 or 11 for 2011. Then append the '20' or '200' to create a year.
Help me with this
Huh ???
If the fourth character contains the year , how is 11 for 2011 being represented in a single character.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply