SQL function to split a string into characters

Question

I hava an ID column in my database, and it shows the results as follows

1121
1232
1233

and i want to get an extra column where i can have their sums as follows

5
8
9

can anyone help me which sql function should i use to break a string into characters and add them?

possible duplicate of [How do I split a string so I can access item x](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — Ocaso Protal, Aug 05 '14 at 11:31

score 1 · Accepted Answer · answered Aug 05 '14 at 11:36

Assuming a number that is always 4 digits long, you can simply do this:

select (id/1000)+((id%1000)/100)+((id%100)/10)+(id%10)

If the ID field is varchar, just cast it to an int before division. Of course, if the result of this has more than 1 digit, you will not be able to get the sum of its digits again.

score 1 · Answer 2 · answered Aug 05 '14 at 11:42

You could use this table-valued function:

CREATE FUNCTION [dbo].[Chars]
(
    @Text NVARCHAR(MAX)
)
RETURNS @ItemTable TABLE (Item VARCHAR(250))  
AS      

BEGIN    
    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @i = 1

    WHILE (@i <= LEN(@Text))
    BEGIN
        INSERT INTO @ItemTable(Item) 
        VALUES(SUBSTRING(@Text, @i, 1))
        SET @i = @i + 1
    END 
    RETURN
END

Now this query should work as desired:

SELECT t.ID, SUM(CAST(Split.Item AS INT)) AS SumID
FROM dbo.TableName t
CROSS APPLY dbo.Chars(CONVERT(varchar(10), t.ID))Split
GROUP BY t.ID

Here's a demo: http://sqlfiddle.com/#!3/8eea7/8/0

+1 for the dynamic approach. IT works for more than 4 digits too. — Nishanthi Grashia, Aug 05 '14 at 11:44

SQL function to split a string into characters

2 Answers2