How to split a word into letters in Sql Server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 24-May-13 0:33am

kirthiga S

Updated 20-Dec-22 4:44am

Add a Solution

3 solutions

Add a Solution

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

Arun Vasu · Answer 1 · 2013-05-24T00:50:00

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

Aarti Meswania · Answer 2 · 2013-05-24T01:14:00

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

Member 15870843 · Answer 3 · 2022-12-20T04:44:00

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

How to split a word into letters in Sql Server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

How to split a word into letters in Sql Server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us