Posted by Erland Sommarskog on 10/08/07 22:14

(sjharri@gmail.com) writes:
> I have an application providing me with multiple headers which I have
> mergerd into one big header (below), this header my not always be the
> same but I need to be able to extract a periodstart and periodend from
> it. The periodstart will always be the third substring from the end
> (or 3rd from right) and the periodend will always be the first
> substring from the end (or 1st from the right).

Given the problems you have, are you really sure that your design is
right? Why not store each field separately, as the relational model
dictates? When you cram more than one piece of information, you
have a problem.

> E.g:- Header
>
>
> 'Jensen Alpha TR UKN GBP BM: Caut Mgd BM (50% FTAllSh 50% ML £ Broad
> Mkt) RF DEF:RFI 3Y 31/08/2004 To 31/08/2007'
>
>
> I currently have the sql: convert(Datetime,
> (dbo.FDHGetWord(@FullHeader, 20)) ,103) but this only works in this
> instance, I need to use someting like the RIGHT function or REVERSE
> function but I can't get the sql right.

SELECT start = MIN(CASE rn WHEN 3 THEN convert(datetime, str, 103) END),
stop = MIN(CASE rn WHEN 1 THEN convert(datetime, str, 103) END)
FROM (SELECT str, rn = row_number() OVER(ORDER BY listpos DESC)
FROM iter_charlist_to_tbl(@FullHeader, ' ')) AS x
WHERE rn IN (1, 3)

You find iter_charlist_to_tbl on
http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-strings


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]