DECLARE @Text AS VARCHAR(100)
SELECT @Text = 'this Is an Example, small ONE'

Declare @textXml Xml
Select @TextXml = Cast('<d>' + Replace(@Text, ' ','</d><d>') + '</d>' As Xml );
SELECT Data FROM 
(
SELECT T.split.value('.', 'nvarchar(max)') AS data, 
CASE WHEN ASCII(LEFT(T.split.value('.', 'nvarchar(max)'),1)) BETWEEN 56 AND 90
AND ASCII(SUBSTRING(T.split.value('.', 'nvarchar(max)'),2,1)) NOT BETWEEN 56 AND 90
 THEN 1 Else 0 END FirstCap 
FROM @textXML.nodes('/d') T (split)
) A 
WHERE FirstCap = 1

quote:

---

Originally posted by vaibhavktiwari83

It will give correct output but is very complex to understand

DECLARE @Text AS VARCHAR(100)
SELECT @Text = 'this Is an Example, small ONE'

Declare @textXml Xml
Select @TextXml = Cast('<d>' + Replace(@Text, ' ','</d><d>') + '</d>' As Xml );
SELECT Data FROM 
(
SELECT T.split.value('.', 'nvarchar(max)') AS data, 
CASE WHEN ASCII(LEFT(T.split.value('.', 'nvarchar(max)'),1)) BETWEEN 56 AND 90
AND ASCII(SUBSTRING(T.split.value('.', 'nvarchar(max)'),2,1)) NOT BETWEEN 56 AND 90
 THEN 1 Else 0 END FirstCap 
FROM @textXML.nodes('/d') T (split)
) A 
WHERE FirstCap = 1

Vaibhav T

If I cant go back, I want to go fast...

---