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