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: