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:
Last Updated :
25 Oct, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...