How to select a word substring in a coumn header from right to left

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).

How can I extract the periodstart and periodend?

Eg:- Header


'Jensen Alpha TR UKN GBP BM: Caut Mgd BM (50% FTAllSh 50% ML =A3 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.

Can someone please help!????

Re: How to select a word substring in a coumn header from right to left

(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-o f-strings


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

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

Re: How to select a word substring in a coumn header from right to left

>> I have an application providing me with multiple headers which I have merged 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).<<

Did you mean "concatenate" and not "merge"? if you wanted these dates
why aren't they parameters to a procedure?

Since headers are a display concept, they have no place in a database
at all! Why not keep throw in the paper size colors to used for the
reports along with the headers??

And the final error is violating ISO-8601 date formats.

>>..but I can't get the SQL right. <<

That certainly is your problem. You have screwed up your design and
your whole approach to RDBMS. Get help from someone who has some idea
of what they are doing.