October 26, 2006 at 10:21 am
i get a list of Quarters that look like following as an input to my proc.
'Q306/Q106/Q406/Q405'
I am using SQL Server2000.
I need to find out whihc one of the above is the least Quarter value.
In the above example it is 'Q405'
The Quarter values that come into the proc keep changing...It could be even 1 or 2 or 3 Quarters etc...
Is there any datatype called Quarter in SQL Server.It shows Quarter as a key word...
Can some one please help on this....This is a very urgent requirememnt for me..
Thanks
October 26, 2006 at 10:36 am
I would use a split function to split the 'Quarter' values into a table variable and then rank them on the value (without the 'Q') converted to int. Search this site for the split function.
October 26, 2006 at 2:38 pm
Does SQL Server 2000 have split function...
Say i have the Quarters like below
'Q106/Q305/Q106 Version1,Q305 Version2'...
Can you let me know how to split each of these values and read the seperate values into a variable...
Can i read Q106 seperately..then Q305...Q106 version1 etc...Does Sql server have a split function
October 26, 2006 at 2:50 pm
No, SQL Server does not have a split function. That is why I told you to seach this site for it. I could give you links to it, but there are many posts out here regarding the split function as well as the underlying numbers table. Use the search functionality and type in 'Spilt Function'. In a nut shell, once you've got the function and the numbers table, you will pass your string into the function and you will then be able to manipulate/order/rank the items, or Quarters, as you wish.
October 27, 2006 at 4:37 am
Well, here is something that should get you started. Doing a
select min(sval) from split('Q406/Q305/Q507/Q104')
returns Q104.
I'll leave it up to you to handle mixed delimeters and variable length items.
create function split(@s varchar(1000)) returns @STAB table(sval varchar(4))
as
begin
declare @ptr int
if len(@s)>0
begin
set @ptr=charindex('/',@s)
while @ptr>0
begin
insert into @STAB values(substring(@s,@ptr+1,4))
set @ptr=charindex('/',@s,@ptr+1)
end
insert into @STAB values(right(@s,4))
end
return
end
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply