Sql Take Just the Numeric Values from a Varchar

Extracting Numeric values from a column (VARCHAR) based on a preqrequisite

The pattern I am trying to identify and extract from a string is as follows

DDDDDDDD-D or DDDDDDDD-DD

D stands for numeric digit

So the following worked for me

SELECT description, 
CASE 
    WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%', description)>0 THEN SUBSTRING(description, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%', description), 11)
    WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]%', description)>0 THEN SUBSTRING(description, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]%', description), 10)
END AS refno, 
*
FROM
XXXXX
where (description like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]%' OR description like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%')

How to get only numeric column values?

SELECT column1 FROM table WHERE ISNUMERIC(column1) = 1

Note, as Damien_The_Unbeliever has pointed out, this will include any valid numeric type.

To filter out columns containing non-digit characters (and empty strings), you could use

SELECT column1 FROM table WHERE column1 not like '%[^0-9]%' and column1 != ''

SQL split only the numeric values from a varchar

If you can guarantee that there will always be a space character between the numbers and the text then use CHARINDEX with SUBSTRING:

SELECT
    SUBSTRING( [Column A], 1, CHARINDEX( ' ', [Column A] ) ) AS Digits
FROM
    myTable

MySQL - Select only numeric values from varchar column

SELECT * 
FROM mixedvalues 
WHERE value REGEXP '^[0-9]+$';

How to extract numeric values from a string in sql

You can create a scalar function:

CREATE FUNCTION [dbo].[ExtractNetAmount]
(
    @FieldText varchar(1000)    
)
RETURNS varchar(50)
AS
BEGIN

    declare @Result decimal(12, 3);

    declare @netIncomeKeword varchar(100) = 'NET INCOME- RS '
    declare @temp1 varchar(100)
    declare @temp2 varchar(100)

    set @temp1 =    SUBSTRING(@FieldText, PATINDEX('%' + @netIncomeKeword +'[0-9]%', @FieldText) + LEN(@netIncomeKeword), LEN(@FieldText))
    set @temp2 =    REPLACE(SUBSTRING(@temp1,0, charindex('/', @temp1)),',','')

    set @Result = CAST(@temp2 as decimal(12, 3))

    RETURN @Result;
END

Assumptions:

1. There must have keyword NET INCOME- RS present in text

2. Amount always ends with /

Usage:

    declare @FieldText varchar(1000) = 'INCOME FROM RETAIL SALE OF VEGETABLE 
BUSINESS RS 30,000/- INCOME FROM WHOLE SALE OF VEGETABLE BUSINESS- RS 40,000/- 
TOTAL INCOME APPROX- RS 70,000/- TOTAL EXPENSES- RS 35,000/- 
[SALARIES, FUEL, OTHER EXPENSES] NET INCOME- RS 35,000/-
'
select dbo.[ExtractNetAmount](@fieldtext) 

It only extracts Net Income value from whole text, i.e. 35000.000 in given example.

Select Only Numbers From Varchar column

You can use a combination of REPLACE and ISNUMERIC to achieve the result set you want:

SELECT REPLACE(columnName,'%','') FROM tableName
WHERE ISNUMERIC(REPLACE(columnName,'%','')) = 1

---

Related Topics