15,967,130 members
See more:
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

SQL
```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```

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```
Posted
Updated 20-Dec-22 4:44am

## Solution 1

SQL
```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```

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

Regards
Arun Vasu

v2

## Solution 2

SQL
```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...
SQL
`select val from dbo.SplitInLetters('siva')`

To get value after join with your letterValues table
SQL
```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```

*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!
:)

v2

## Solution 3

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

create table data_value(letter varchar(50),value int)
Insert into data_value(letter,value) values('a',1)
Insert into data_value(letter,value) values('i',1)
Insert into data_value(letter,value) values('m',3)
Insert into data_value(letter,value) values('r',2)
Insert into data_value(letter,value) values('s',2)
Insert into data_value(letter,value) values('v',3)

select * from data_value
select * from Data

Select Name , sum(value) as sumvalue
from
(
Select Name,ss.chr, dv.value
from Data
Cross apply   (
Select chr From
(
Select top(len(Name)) substring(Name, row_number() over(order by 1/0), 1) as chr
from sys.messages
)A(chr)
)ss
inner join data_value dv on dv.letter = ss.chr
)d group by Name```

v2
Richard Deeming 20-Dec-22 10:58am
If you're going to resurrect a question from almost 10 years ago:

1) Format your code - either wrap it in `<pre>` tags manually, or select it all and click the "code" button on the toolbar;

2) Explain your code - specifically why you think it's better than the existing solutions;

Just dumping unexplained and unformatted code as a solution to an ancient question will get you banned from the site.