从一个表中获取单词并将其放入sql server中的另一个表中. [英] getting the words from one table and putting them into another table in sql server.

问题描述

亲爱的所有人，

我有一个名为"customer"的表，该表具有"customer_name"列.

我想从customer_name列中获取特定单词并将其放入
另一个表，例如"word_count"，它将具有两列"word"和"count".

举例来说，客户"表中客户名称"列中的客户名称是约翰·大卫·彼得森".

在这里，我想分别获得三个单词，即"John"，"David"和"Peterson"，并将它们放在称为"word_count"的表中.

"word_count"表应具有这三个单词，如下所示:
字数
约翰福音1
大卫1
彼得森1

任何人都可以给我一个想法来为此编写查询或存储过程吗?

此致

Dear All,

I have a table named "customer" which has a column "customer_name".

I want to get the particular word from the customer_name column and put it into
another table, say "word_count" which will be having two columns "word" and "count".

Say for example, The customer name in column "customer_name" is "John David Peterson" in "customer" table.

Here, I want to get the three words separately, i.e., "John", "David" and "Peterson" and put them in a table called "word_count".

The "word_count" table should have those 3 words as follows:
Word count
John 1
David 1
Peterson 1

Can any one please give me an idea to write the query or stored procedure for this?

Regards,

推荐答案

我想

I guess this[^] should be useful to you.

可以在SQL中拆分单词或句子，尽管这不是内置函数.但是，在网上搜索某些功能会返回大量结果.
SQL拆分功能1 [ SQL拆分功能2 [ ^ ]
SQL拆分功能3 [

It is possible to split words or sentences in SQL, though this is not a built-in function. However, searching the net for some functions returns numerous results.
SQL Split Function 1[^]
SQL Split Function 2[^]
SQL Split Function 3[^]
Etc...

I haven''t used or tested those, but they generally work something like:

--The function returns a table, so you can select from it.
select * from dbo.SplitFunction("This is a test String", ' ')

/*
Return something like:
This
is
a
test
String
*/

这是我发现的返回表的SQL字符串拆分函数.

Here''s a string splitting function for sql I found that returns a table.

CREATE FUNCTION [dbo].[SDF_SplitString] 
(
    @sString nvarchar(2048),
    @cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) ) 
AS BEGIN     
    if @sString is null return     
    declare @iStart int, @iPos int     
    if substring( @sString, 1, 1 ) = @cDelimiter     
    begin         
        set @iStart = 2
        insert into @tParts values( null )
    end
    else
        set @iStart = 1
    while 1=1
    begin
        set     @iPos = charindex( @cDelimiter, @sString, @iStart )         
        if @iPos = 0                 
            set     @iPos = len( @sString )+1         
        if @iPos - @iStart > 0                                   
            insert into @tParts values  ( substring( @sString, @iStart, @iPos-@iStart ))         
        else                 
            insert into @tParts values( null )
        set @iStart = @iPos+1
        if @iStart > len( @sString )
            break
    end
    RETURN  
END 

这篇关于从一个表中获取单词并将其放入sql server中的另一个表中.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！