CODEWITHSUNDEEP
sqlsql-server-2008
Abdullah A Malik
Abdullah A Malik

Reputation: 354

SQL function to split a string into characters

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

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

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

Upvotes: 1

Views: 1726

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460208

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

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

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.

Upvotes: 1

Related Questions