如何使用 MS SQL 获取所有字段记录中使用的不同单词列表? [英] How to get a distinct list of words used in all Field Records using MS SQL?

问题描述

如果我有一个名为description"的表字段，获取该字段中使用的所有不同单词的记录列表的 SQL(使用 MS SQL)是什么.

If I have a table field named 'description', what would be the SQL (using MS SQL) to get a list of records of all distinct words used in this field.

例如:

如果表格的描述"字段包含以下内容:

If the table contains the following for the 'description' field:

Record1 "The dog jumped over the fence."
Record2 "The giant tripped on the fence."
...

SQL 记录输出为:

"The","giant","dog","jumped","tripped","on","over","fence"

推荐答案

我不认为您可以使用 SELECT 来做到这一点.最好的机会是编写一个用户定义的函数，该函数返回一个包含所有单词的表，然后对其执行 SELECT DISTINCT.

I do not think you can do this with a SELECT. The best chance is to write a user defined function that returns a table with all the words and then do SELECT DISTINCT on it.

免责声明:函数 dbo.Split 来自 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

CREATE TABLE test
(
    id int identity(1, 1) not null,
    description varchar(50) not null
)

INSERT INTO test VALUES('The dog jumped over the fence')
INSERT INTO test VALUES('The giant tripped on the fence')

CREATE FUNCTION dbo.Split
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

CREATE FUNCTION dbo.SplitAll(@SplitOn nvarchar(5))
RETURNS @RtnValue table
(
    Id int identity(1,1),
    Data nvarchar(100)
)
AS
BEGIN
DECLARE My_Cursor CURSOR FOR SELECT Description FROM dbo.test
DECLARE @description varchar(50)

OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @description
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @RtnValue
    SELECT Data FROM dbo.Split(@description, @SplitOn)
   FETCH NEXT FROM My_Cursor INTO @description
END
CLOSE My_Cursor
DEALLOCATE My_Cursor

RETURN

END

SELECT DISTINCT Data FROM dbo.SplitAll(N' ')

这篇关于如何使用 MS SQL 获取所有字段记录中使用的不同单词列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！