CODEWITHSUNDEEP
sql-server
Dan Nick
Dan Nick

Reputation: 426

Select String based on capital words

I am trying to do something similiar to this https://stackoverflow.com/a/11058109/1383668. The difference is I have entire words that are capital and dynamic. This is the data

THE box is on the desk by the door WILLIAM ran ten miles in world record time DO you know the way to San Jose APPLES to apples is a baking term that is used often when making a pie

This is how it should look when I run it.

THE box is on the desk by the door 
WILLIAM ran ten miles in world record time 
DO you know the way to San Jose 
APPLES to apples is a baking term that is used often when making a pie

Upvotes: 0

Views: 47

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

With the aid of a split/parse function

To be clear, I added a little bit of logic to trap single letter capital words such as I. len(RetVal)>1

To Aaron's point, double words may require a lead/lag trap.

Example

Declare @S varchar(max) = 'THE box is on the desk by the door WILLIAM ran ten miles in world record time DO you know the way to San Jose APPLES to apples is a baking term that is used often when making a pie'

;with cte as (
Select *
      ,grp = sum(case when len(RetVal)>1 and upper(RetVal)=RetVal COLLATE SQL_Latin1_General_CP1_CS_AS then 1 else 0 end) over (order by RetSeq)
 From  [dbo].[tvf-Str-Parse](@S,' ')
)
Select RN = Grp
      ,NewValue = Stuff((Select ' ' +RetVal From cte Where Grp=A.Grp Order By RetSeq For XML Path ('')),1,1,'') 
 From  cte A
 Group By Grp

Returns

RN  NewValue
1   THE box is on the desk by the door
2   WILLIAM ran ten miles in world record time
3   DO you know the way to San Jose
4   APPLES to apples is a baking term that is used often when making a pie

The TVF if Interested

CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = row_number() over (order by 1/0)
          ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  ( values (cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.'))) as A(x)
    Cross Apply x.nodes('x') AS B(i)
);

EDIT - If you don't want to add the Table-Valued Function

;with cte as (
Select *
      ,grp = sum(case when len(RetVal)>1 and upper(RetVal)=RetVal COLLATE SQL_Latin1_General_CP1_CS_AS then 1 else 0 end) over (order by RetSeq)
 From  (
       Select RetSeq = row_number() over (order by 1/0)
              ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
        From  ( values (cast('<x>' + replace((Select replace(@S,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.'))) as A(x)
        Cross Apply x.nodes('x') AS B(i)
       ) A
)
Select RN = Grp
      ,NewValue = Stuff((Select ' ' +RetVal From cte Where Grp=A.Grp Order By RetSeq For XML Path ('')),1,1,'') 
 From  cte A
 Group By Grp

Upvotes: 2

Related Questions