如何在Sql Server中将单词拆分为字母 [英] How to split a word into letters in Sql Server

问题描述

大家好，



我想将一个单词拆分成字母，并为每个字母赋值，并需要指定值的总和。



例如

Hi All,

I want to split a word into letters and assign value to each letter and need the sum of assigned values.

For Example

create table Data (Name varchar(50))
insert into Data values ('siva')
insert into Data values ('Ram')

select * from Data

如果我为字母赋值

If i assign value to letters

a-->1
i-->1
m-->3
r-->2
s-->2
v-->3

我要求的输出是



SIVA - > 2 + 1 + 3 + 1 - > 7

R A M - > 2 + 1 + 3 - > 6

My required output is

S I V A --> 2+1+3+1 -->7
R A M --> 2+1+3 -->6

Name Values
siva 7
ram  6

推荐答案

CREATE PROCEDURE Proc_SplitWordsToChar
@Sentence VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON

 DECLARE @Words VARCHAR(MAX)
 DECLARE @t VARCHAR(MAX)
        DECLARE @I INT
 
    SET @Words = @Sentence    
    SELECT @I = 0
 
    WHILE(@I < LEN(@Words)+1)
    BEGIN
      SELECT @t = SUBSTRING(@words,@I,1)
   
      PRINT @t
      SET @I = @I + 1

    END
   
END

这个程序用于将单词分成字符...

这会有所帮助。



问候

Arun Vasu

This procedure is used to split word into character...
This will helpful for a bit.

Regards
Arun Vasu

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitInLetters]
(@String varchar(8000))
RETURNS @RESULTS TABLE (Val varchar(1))
AS
BEGIN
	Declare @Counter int
	set @Counter =1 
	DECLARE @Value varchar(max)
    WHILE @Counter <=  len (@string)-- is not null
    BEGIN
        SELECT @Value= substring (@String,@counter,1) 
        INSERT INTO @RESULTS (Val)
        set @counter = @counter + 1
        SELECT @Value
    END
END

简单使用...

To simple use...

select val from dbo.SplitInLetters('siva')

在使用letterValues表加入后获取值

To get value after join with your letterValues table

select sum(lv.letterVal) from
(
select a.val,lv.letterVal 
from dbo.SplitInLetters('siva') as a
left join tblLetterValues as lv on a.val = lv.letter
) as a

*注意您可以在SplitInLetter函数中添加上述查询并进行一些更改然后你可以直接返回一个字母的总和



快乐编码！

：）

*Note you can add above query in SplitInLetter function with some changes then you can directly return sum of letters in a word

Happy Coding!
:)

这篇关于如何在Sql Server中将单词拆分为字母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！