Return word with the capital letter from string

I have a column like:

column1
-------------
this is a New world
this is a kind Person
this is a good Idea

How to get:

column1
------------------
New
Person
Idea

Assume there is only one word with the capital letter per string.

Answer

You would need PATINDEX (to find any of the capital letters within the string), CHARINDEX (to find the position of space after the capital letter or end of the string) and SUBSTRING (to get the part of the whole string).

try the following:

declare @tab table (column1 varchar(100))
insert into @tab select 'this is a New world'
insert into @tab select 'this is a kind Person'
insert into @tab select 'this is a good Idea'

select * from @tab

select substring(column1, PATINDEX('%[A-Z]%', column1 COLLATE Latin1_General_BIN)
                , case when CHARINDEX(' ', substring(column1, PATINDEX('%[A-Z]%', column1 COLLATE Latin1_General_BIN), len(column1)), 1) = 0
                        then len(column1) else CHARINDEX(' ', substring(column1, PATINDEX('%[A-Z]%', column1 COLLATE Latin1_General_BIN), len(column1)), 1)-1 end) as column1
from @tab