Extracting nth word in a string

[contracer]

Hi,
Please, can you help me how extract nth word in string ccs.errormessage ?


Select n.caption, ccs.errormessage FROM APM_CurrentComponentStatus ccs WITH (NOLOCK)
INNER JOIN APM_Application a WITH (NOLOCK) ON ccs.ApplicationID = a.ID
INNER JOIN Nodes n WITH (NOLOCK) ON a.NodeID = n.NodeID
where ccs.ErrorMessage LIKE '%hit%'
order by 2 asc




ORACLE01 FS /apl/orawpsp hit 90% usage -> 533 MB available
FS /bd1/orawppdata/sys hit 90% usage -> 199 MB available

[rja.ca...@gmail.com]

On Saturday, 11 May 2013 14:21:35 UTC+1, contracer wrote:
> Hi,
>
> Please, can you help me how extract nth word in string ccs.errormessage ?

I think /that's/ a case to write a user-defined function,
particularly if n isn't fixed. In the function, you can
use CHARINDEX(' ', @message, @previousSpace+1) to find
each word break - if the word break is a single space.

[Erland Sommarskog]

One possibility is to use a string-to-table that numbers the
elements, and then filter on row number.

(SELECT str FROM string_to_table(ccc.errormessage) WHERE row_number = @n)

I have a number of such functions in this article on my web site:
http://www.sommarskog.se/arrays-in-sql-2005.html


--
Erland Sommarskog, Stockholm, esq...@sommarskog.se