如何在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屋!