SQL Query to Get Only Numbers From a String

Last Updated : 25 Oct, 2021

As we know in an SQL database we can insert any type of data. Sometimes in the productions server, the data gets corrupted by two or more rows being merged and being saved in a column. In that case, we can extract the numeric part from that string and save it again. So in this article, we will learn how to extract numeric parts of a string in SQL. We will make use of Microsoft SQL as our server.

So let's start by creating a database First.

Step 1: Create DB

Query:

CREATE DATABASE GFG

Step 2: Use this DB

Query:

USE GFG

Step 3: Create a table

Create a table (GetNum) to store the data

Query:

CREATE TABLE GetNum(   StudentName varchar (255)  )

Step 4: Insert some data into the database

Query:

 INSERT INTO GetNum (StudentName) VALUES ('Devesh123')      INSERT INTO GetNum (StudentName) VALUES ('Geeks2')      INSERT INTO GetNum (StudentName) VALUES ('For5')      INSERT INTO GetNum (StudentName) VALUES ('Aman98')

Output:

Step 4: SQL query to extract the numbers

We will write a SQL function in order to not run the same query again and again for extracting the numbers, we can use that function multiple times also it will be stored in the database.

Query:

CREATE FUNCTION dbo.getNumericValue   (  @inputString VARCHAR(256)  )  RETURNS VARCHAR(256)  AS  BEGIN    DECLARE @integerPart INT    SET @integerPart = PATINDEX('%[^0-9]%', @inputString)    BEGIN      WHILE @integerPart > 0      BEGIN        SET @inputString = STUFF(@inputString, @integerPart, 1, '' )        SET @integerPart = PATINDEX('%[^0-9]%', @inputString )      END    END    RETURN ISNULL(@inputString,0)  END  GO

You can refer for more details for PATINDEX().

Step 5: Now run the created function to get the desired output.

Query:

SELECT dbo.getNumericValue(StudentName) from GetNum

Output:

SQL Query to Get Column Names From a Table

idevesh

Improve

Article Tags :

SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database, etc. SQL is an ANSI(American National Standards Institute) standard. Using SQL, we can do ma

2 min read

SQL Query to Match Any Part of String

It is used for searching a string or a sub-string to find a certain character or group of characters from a string. We can use the LIKE Operator of SQL to search sub-strings. The LIKE operator is used with the WHERE Clause to search a pattern in a string of columns. The LIKE operator is used in conj

3 min read

SQL Query to convert NUMERIC to NVARCHAR

Here we will see, how to convert NUMERIC data to NVARCHAR data in a MS SQL Server's database table using the CAST(), CONVERT() and FORMAT() functions. We will be creating a person table in a database called "geeks". Creating the Database:CREATE DATABASE geeks;Using the Database:USE geeks;Table Defin

2 min read

SQL Query to Convert Datetime to String

In order to convert a DateTime to a string, we can use CONVERT() and CAST() function. These functions are used to converts a value(of any datatype) into a specified datatype. CONVERT() Function Syntax: CONVERT(VARCHAR, datetime [,style])VARCHAR - It represent the string type.datetime - It can be the

3 min read

How to Insert Line Break in SQL Server String?

In SQL Server there are various datatypes like int, float, char, nchar, etc but especially while we are dealing with text in VARCHAR and NVARCHAR columns, we might run into situations where we need to make the text look cleaner by adding line breaks. This could be for better organization, and readab

4 min read

Convert Strings to Numbers and Numbers to Strings in Python

Converting strings to numbers and numbers to strings is a common task in Python. In this article, weâ€™ll explore simple ways to convert between strings and numbers . Using int() and str()int() and str() functions in Python are commonly used for converting data types. The int() function changes a stri

3 min read

SQL Query to Compare Two Strings

SQL stands for Structured Query Language. It is used to communicate with the database. There are some standard SQL commands like 'select', 'delete', 'alter' etc. To compare two strings in SQL Server, there is no direct way. In this article, we will learn how to compare two strings in an MS SQL serve

2 min read

How to extract Numbers From a String in PHP ?

Extracting numbers from a string involves identifying and isolating numerical values embedded within a text. This process can be done using programming techniques, such as regular expressions, to filter out and retrieve only the digits from the string, ignoring all other characters. Here we have som

3 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

Extracting Unique Numbers from String in R

When working with text data in R, you may encounter situations where you need to extract unique numbers embedded within strings. This is particularly useful in data cleaning, preprocessing, or parsing text data containing numerical values. This article provides a theoretical overview and practical e

4 min read

SQL Query to Get Only Numbers From a String

Similar Reads