从一个表中获取单词并将其放入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,

推荐答案

--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
*/

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中的另一个表中.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！