Archive for December, 2009
Oracle Timestamp Difference in seconds
by ivonko on Dec.23, 2009, under Development
select (TO_NUMBER(TO_CHAR(EndDate,’J’))-TO_NUMBER(TO_CHAR(CreateDate,’J’)))*86400 + (TO_NUMBER(TO_CHAR(EndDate,’SSSSS’))-TO_NUMBER(TO_CHAR(CreateDate,’SSSSS’))) AS “Delta (sec)” from table1
MSSQL function charindex-at-n-position
by ivonko on Dec.17, 2009, under Development
To find the charindex of an expression “_” in n position say 3rd occurancein a given word ‘xx_xxx_xx_xx’
Result : 10
SELECT dbo.fn_charIndex(‘_’,3,’ xx_xxx_xx_xx’)
—————————————————————
And the code for creating function:
Create function dbo.fn_charIndex(@Expression varchar(256),@nPos int,@Word varchar(2000))
returns int
as
begin
declare @ret int,@strt int
set @ret = 0
set @strt = 0
while not @strt = @npos
begin
select @ret = charindex(@Expression,@Word,@ret+1)
set @strt = @strt +1
end
return @ret
end
—————————————————————
Source: http://www.calsql.com/2009/10/function-charindex-at-n-position.html
