How to read third word from a string?

Hallow, everyone:
I want to read third word from a string. For example, the string is,
ABCDE fghijk 031 LPN OPQ
I need 031. How to read it?
Any help will be appreciated.
Thanks
ZYTOk, do I win a prize for finding the ugliest, most cumbersome way to do this? :)

declare @.strtest as varchar(100)
set @.strtest='ABCDE fghijk 031 LPN OPQ'
select substring(@.strtest,charindex(' ', @.strtest, charindex(' ',@.strTest,1)+1), charindex(' ', @.strtest,charindex(' ', @.strtest, charindex(' ',@.strTest,1)+1)+1)-charindex(' ', @.strtest, charindex(' ',@.strTest,1)+1))

Of course, it crashes if there are only three words to begin with.|||I'm sure there is an easier way..but here's my offering

DECLARE @.x varchar(8000)

SET @.x = 'ABCDE fghijk 031 LPN OPQ'

SELECT SUBSTRING(@.x
, CHARINDEX(' ',@.x,CHARINDEX(' ',@.x)+1)+1
, (CHARINDEX(' ',@.x,CHARINDEX(' ',@.x,CHARINDEX(' ',@.x)+1)+1)-1)
- (CHARINDEX(' ',@.x,CHARINDEX(' ',@.x)+1)+1)+1)|||I think you would have to add a space to the end of the string, just to make sure it always works, even if there are only 3 words.|||parsing strings is so much easier in compiled code. forgive me for saying this but sql sucks at it. what sql is good at is set based operations.

you should do this on the client if you can:

string[] s = mystring.Split(' ');
string third = s.Length >= 3 ? s[2] : null;|||that is what is listed in Inside T-SQL Programming as one of the justifications for implementing CLR and I looked over the performance metrics and played with it myself. but I am still of the mind that if it requires C# code do it in the application. we are prepping for the whole 2K5 thing, and one the things that scares me is the potential abuse that CLR leaves open. in sql 2k, i had to worry about bad sql which I could fix. Now I might have to worry about bad C# in my sql server process and it has been too long since I really programmed app code.|||that is what is listed in Inside T-SQL Programming as one of the justifications for implementing CLR and I looked over the performance metrics and played with it myself. but I am still of the mind that if it requires C# code do it in the application. we are prepping for the whole 2K5 thing, and one the things that scares me is the potential abuse that CLR leaves open. in sql 2k, i had to worry about bad sql which I could fix. Now I might have to worry about bad C# in my sql server process and it has been too long since I really programmed app code.

any technology can be abused. that's not necessarily a reason not to use it though. it can be a very powerful thing for stuff that sql is not designed for, such as string parsing or complicated math.

if you are not comfortable with C# or some other .net language though, I wouldn't recommend using CLR in sql server. why not learn it though? it's always good to grow. :)|||Here's so more abuse

CREATE FUNCTION udf_GetWord (
@.str nvarchar(4000)
, @.Word int
, @.Delim char(1)
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @.LastPosition int, @.DelimFound int, @.Start int, @.End int, @.WordFound nvarchar(4000)

SELECT @.LastPosition = 0, @.DelimFound = 0
WHILE (@.DelimFound < @.Word-1)
BEGIN
IF (CHARINDEX(@.Delim, @.str, @.LastPosition + 1) = 0)
BREAK

ELSE
BEGIN
SET @.LastPosition = CHARINDEX(@.Delim, @.str, @.LastPosition + 1)
SET @.DelimFound = @.DelimFound + 1
END

END

SET @.Start = @.LastPosition + 1
SET @.End = CHARINDEX(@.Delim, @.str, @.LastPosition + 1) - @.Start
IF @.End < 0 SET @.End = LEN(@.str)-@.start+1
SELECT @.WordFound = SUBSTRING(@.str,@.start,@.end)
RETURN @.WordFound
END|||any technology can be abused. that's not necessarily a reason not to use it though. it can be a very powerful thing for stuff that sql is not designed for, such as string parsing or complicated math.

if you are not comfortable with C# or some other .net language though, I wouldn't recommend using CLR in sql server. why not learn it though? it's always good to grow. :)

the average dba makes more than the average developer and no one is going to pay me to code C# right now. I actually spent the first few years of my programming career as a vb \ java \ classic asp developer but it has been all dba for a while now. lets see, should I take the entry level c# developer job with a paycut or should I take the good paying mid-level\senior dba job? hmmmmmm. I am only into my 2nd sql 2k5 book, so I am far enough behind in what I am decent at.|||I wasn't suggesting that you quit and start fresh as a dime-a-dozen programmer. just that you could learn C# if you wanted to. sounds like you don't want to though.

in that case stick with parsing strings in sql. it works, it's just not the most efficient way. ;)|||It's a front end issue...let the minions handle it|||I wasn't suggesting that you quit and start fresh as a dime-a-dozen programmer. just that you could learn C# if you wanted to. sounds like you don't want to though.

in that case stick with parsing strings in sql. it works, it's just not the most efficient way. ;)

I hear you but the only way to get good is to do it everyday and I just do not have that opp. I actually picked up Inside C# and got myself a compiler last summer and frankly I got bored with it.|||fair enough.

about getting bored though, i think that's your own fault (or maybe the books fault - I hardly ever read tech books), not the fault of the language. there are lots of interesting and fun things you can do in C/C++/C#.

Here's just one tiny example: implement an RDBMS. your beloved SQL Server is implemented in those languages. ;)