如果我有一个名为“ description”的表字段,那么什么是SQL(使用MS SQL)来获取该字段中使用的所有不同单词的记录列表。
例如:
如果表格的“说明”字段包含以下内容:
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。
免责声明: 函数 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' ')