How To Find the Sum of Digits in a String in SQL Server?
Last Updated :
11 Apr, 2023
Given a string with digits and characters. The task is to find the sum of digits in that string.
So, let’s start by creating a database first.
Step 1: Create a Database.
Query :
CREATE DATABASE GFG
Step 2: Use the GFG Database.
Query :
USE GFG
Step 3 :
 a) Select each character as a row by traversing that string using Recursive CTE.
 b) We will get the value by summing up the rows of CTE in which the character is numeric.
Query :
DECLARE @count INT,
@str1 VARCHAR(20),
@l1 INT;
SET @count = 1;
SET @str1 = '9fghjk8fghj66rt5';
SELECT
@l1 = LEN(@str1);
WITH CTE(col, cou) AS(
SELECT
SUBSTRING(@str1, @count, 1),
@count
UNION ALL
SELECT
SUBSTRING(@str1, cou + 1, 1),
cou + 1
from
CTE
WHERE
cou < @l1
)
SELECT
SUM(CAST(col AS INT))
FROM
CTE
WHERE
ISNUMERIC(col)= 1;
Output :
The digits in that string are 9,8,6,6,5.
9+8+6+6+5=34
Â
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...