I have a column like:
x
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.
Advertisement
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