将sql字符串拆分为单词 [英] Split sql string into words

问题描述

我想把字符串拆分成如下的单词，所有字符串的输出应该是一样的:

I want to split string into words like below, the output of all the string should be same:

输入:

 1. This is a string
 2. This    is   a   string
 3. This  is a          string
 4. This  is           a string

输出:

这是一个

意思是，我想要句子中的前三个单词，不考虑空格.

Means, that I want first three words from the sentence, irrespective of the spaces.

推荐答案

试试这个:

declare @s1 varchar(3000) ;
declare @xml xml,@str varchar(100),@delimiter varchar(10), @out varchar(max);;
select @delimiter =' '
select @s1 =  'This is a string';
select @s1 = 'This    is   a   string ';
select @s1 = 'This  is a          string ';
select @s1 = 'This  is           a string';

select @xml = cast(('<X>'+replace(@s1,@delimiter ,'</X><X>')+'</X>') as xml)

select top 3 @out = 
    COALESCE(@out + ' ', '') +  C.value('.', 'varchar(100)') 
from @xml.nodes('X') as X(C)
where LEN(C.value('.', 'varchar(10)')) > 0

select @out

这篇关于将sql字符串拆分为单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！