代码之家 › 专栏 › 技术社区 › Eduardo Rascon

如何在SQL Server 2008中从字段中获取单词

sql-server-2008 tsql sql-server sql

Eduardo Rascon · 技术社区 · 14 年前

我需要获取文本字段中的单词,并使用这些单词进行一些更新,例如:

words        | other field    | another field
---------------------------------------------
white        |                |
some words   |                |
some other w |                |

期望结果

words        | other field    | another field
---------------------------------------------
white        |                |
some         | words          |
some         | other          | w

额外的

我有一个查询,在这里我得到一个字段有多少个单词

select nombre, 
       LEN(words) - LEN(REPLACE(words, ' ', ''))+1 as palabras
  from origen_informacion
 where words <> ''

2 回复 | 直到 14 年前

Martin Smith 14 年前

在SQL Server 2008中,您可以使用 sys.dm_fts_parser cross apply .

DECLARE @data TABLE 
(
id INT IDENTITY(1,1) PRIMARY KEY,
words VARCHAR(1000),
other_field VARCHAR(1000),
another_field VARCHAR(1000)
)

INSERT INTO @data (words) 
VALUES ('white'),('some words'),('some other w '),('This sentence has 5 words');

WITH splitData AS
(
SELECT 
       id ,
       max(case when occurrence = 1 then display_term end) as word1,
       max(case when occurrence = 2 then display_term end) as word2,
       max(case when occurrence = 3 then display_term end) as word3,
       max(case when occurrence = 4 then display_term end) as word4       
FROM @data
CROSS APPLY sys.dm_fts_parser('"' + REPLACE(words,'"','') + '"',1033,NULL,0)
GROUP BY id
HAVING MAX(occurrence) <= 4
)
UPDATE @data
SET words = word1, other_field=word2, another_field=word3 + ISNULL(' ' + word4,'')
FROM @data d1
JOIN  splitData sd ON d1.id = sd.id

SELECT * FROM @data

输出

id     words                          other_field     another_field
------ ------------------------------ --------------- --------------
1      white                          NULL            NULL
2      some                           words           NULL
3      some                           other           w
4      This sentence has 5 words      NULL            NULL

BenMorel Manish Pradhan 11 年前

如果要拆分空格分隔的字符串,可以使用以下函数:

create function fn_ParseCSVString
(
@CSVString  varchar(8000) ,
@Delimiter  varchar(10)
)
returns @tbl table (s varchar(1000))
as
/*
select * from dbo.fn_ParseCSVString ('qwe rew wer', ',c,')
*/
begin
declare @i int ,
    @j int
    select  @i = 1
    while @i <= len(@CSVString)
    begin
        select  @j = charindex(@Delimiter, @CSVString, @i)
        if @j = 0
        begin
            select  @j = len(@CSVString) + 1
        end
        insert  @tbl select substring(@CSVString, @i, @j - @i)
        select  @i = @j + len(@Delimiter)
    end
    return
end


GO

并将“”作为分隔符传递。

select * from dbo.fn_ParseCSVString ('qwe rew wer', ' ')