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

Combine Rows into String in SQL Server

vandanakillari54935

Improve

Article Tags :

Practice Tags :

Misc

SQL stands for a structure query language, which is used in the database to retrieve data, update and modify data in relational databases like MySql, Oracle, etc. And a query is a question or request for data from the database, that is if we ask someone any question then the question is the query. S

4 min read

How to Get First Character of a String in SQL?

SQL (Structured Query Language) is essential for managing and querying relational databases. Whether you're handling customer data, employee records, or product details, SQL provides powerful tools for manipulating string data. One common task when working with strings is to extract the first charac

4 min read

Combine Rows into String in SQL Server

To combine rows into a string in SQL Server, use the SQL COALESCE() function or the SQL CONCAT() function. COALESCE() function in SQL is used to handle null values. It returns non-null values from a row, which can be concatenated into string. CONCAT() function in SQL is used to concatenate two or mo

2 min read

How to Find the Missing Number in SQL Column?

Given a column in the table having values from 1 to N, one value will be missed. The task is to find the missing number. So, let's start by creating a database first. Step 1 : Create Database. Query : CREATE DATABASE GFG Step 2 : Use the GFG Database. Query : USE GFG Step 3 : Create a table Create a

1 min read

How to Find the Length of a Series Using Window Functions in SQL

In data analysis, understanding the sequential patterns within datasets is crucial for informed decision-making. SQL's window functions offer a powerful toolset for such tasks, allowing efficient calculations across related rows. This article delves into utilizing window functions to determine the l

5 min read

Sum of digits of a number in PL/ SQL

Prerequisite - PL/SQL introduction In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Given a number and task is to find the sum of digits of

1 min read

SQL Query to Find the Sum of all Values in a Column

In SQL, calculating the sum of values in a column is a crucial task for performing data analysis and generating reports. The SUM() function helps to calculate the total sum of numeric values from a column, which is especially useful in scenarios like finding total sales, total employees, or total re

5 min read

How to Remove the First Characters of a Specific Column in a Table in SQL?

Here we will see, how to remove the first characters of a specific column in a table in SQL. We can do this task using the String function. String functions are used to perform an operation on an input string and return an output string. There are various string functions like LEN(for SQL server), S

3 min read

How to Find Average Marks of Each Student in SQL?

In SQL, sometimes we need to find the average value of a column based on another column of the table such as finding the student-wise average marks scored by him/her in all the subjects. This involves the use of the GROUP BY clause along with the AGGREGATE function like AVG. The same is depicted in

2 min read

Sum of the first and last digit of a number in PL/SQL

1 min read

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

Similar Reads