Click here to Skip to main content
15,867,869 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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
 
Share this answer
 
v2
Comments
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.
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!
:)
 
Share this answer
 
v2
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
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900