SQL Query to Get Only Numbers From a String

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:

idevesh

Follow

Improve

Article Tags :

• SQL
• SQL-Server