How To Find the Sum of Digits in a String in SQL Server?

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