Comment puis-je récupérer la première deuxième et troisième mot d'une chaîne dans SQL?

Si vous ne voulez pas créer de fonction dédiée, vous pouvez utiliser Successive Cross Appliquer Code> S:

|                   s                    | i | j | k  | phrase           |
|----------------------------------------|---|---|----|------------------|
| ATV BDSG 232 Continue with other words | 4 | 9 | 13 | ATV BDSG 232     |

 --make some test data

declare @test as nvarchar(100) = 'my test   string  for words';

select 1 id, cast('my test   string  for words' as nvarchar(max)) word into #test;
insert #test (id,word) values (2,'a  b c d e f g hhh   yyyyyy') ;
insert #test (id,word) values (3,'  a required test string  d e f g hhh   yyyyyy') ;
insert #test (id,word) values (4,'a quick test') ;
insert #test (id,word) values (5,'a test') ;
insert #test (id,word) values (6,'last') ;

--break up letters, count the first 3 words
;WITH CTE AS (SELECT 1 x, substring(@test,1,1) charx
             UNION ALL
             SELECT X + 1, substring(@test,x + 1,1) from CTE WHERE x < len(@test) 
             )
             select * from cte c3 where (SELECT count(0) cnt FROM CTE c1 JOIN CTE c2 on c1.x <= c3.x and c1.x + 1 = c2.x and c1.charx =' ' and c2.charx != ' ') < 3



;WITH   tabx as (select id, cast(ltrim(word) as nvarchar(max)) 'word' from #test),    --do some ltrim
        CTE AS (
                SELECT id, 1 x, substring(word,1,1) charx from tabx
                UNION ALL
                SELECT t.id, c.X + 1, substring(t.word,x + 1,1)  
                        from tabx t 
                            JOIN CTE c on c.id =  t.id and x < len(t.word) 
             ),
        disj as
             (select * from cte c3 where 
                        (SELECT count(0) cnt 
                                FROM CTE c1 
                                JOIN CTE c2 on c1.id = c3.id and c1.id = c2.id and c1.x <= c3.x and c1.x + 1 = c2.x and c1.charx =' ' and c2.charx != ' '
                        ) < 3
                ),
        rj as 
            (select disj.id,disj.x, disj.charx z  
                                from disj 
                                where disj.x = 1 
                UNION ALL 
                    select d.id, d.x, r.z + d.charx    
                            FROM rj r 
                            join disj d on r.id = d.id and r.x + 1 = d.x
            )
                select *  
                    from rj r1  
                        cross apply (select max(r2.x) TheRow from rj r2 where r1.id = r2.id) dq
                    where r1.x = dq.TheRow  
                    order by r1.id;

 --delete test data
drop table #test 

/* This is not perfect - but interesting */
declare @t table (fullname varchar(100))
insert @t values('Mr Jones'),('Mrs Amy smith'),('Jim Smith'),('Dr Harry Web '),('Paul Fred andrew jones')

select fullname,

a.value as a ,
b.Value as b,
c.Value as c,
d.Value as d,
e.Value as e,
f.value as f

from @t
outer apply (select top 1 value from STRING_SPLIT(fullname, ' '))  a 
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value ))  b
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value,b.value ) ) c
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value,b.value,c.value ))  d
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value,b.value,c.value,d.value) ) e
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value,b.value ,c.value,d.value,e.value) ) f

Pour sélectionner le premier mot - xxx

pour sélectionner seulement un second mot - xxx