Get null if non numeric or the actual numeric value in TSQL

Question

I'm trying to get the numeric value of a string if isnumeric() function returns 1 or NULL if it returns 0. But I only get if it's numeric I get 1 or null if non numeric. Is it possible to return the numeric value (instead of 1) using something like the code below?

select '14-154877-0' as actual_string, replace('14-154877-0', '-', '') as numeric_value, nullif(isnumeric(replace('14-154877-0', '-', '')), 0) as numeric_value_or_null /* Here I wold like to return the numeric value instead of 1 */

select 'some text' as actual_string, replace('some text', '-', '') as numeric_value, nullif(isnumeric(replace('some text', '-', '')), 0) as numeric_value_or_null /* OK */

Sample data

The insert statements are a result of the excel concatenation function.

As sugested, I used the case expression and the try_convert() (for MSSQL 2012) function and they work fine. Is there a better way of doing this kind of insert?

if object_id('tempdb..#temp_table') is not null
    begin
        drop table #temp_table;
    end;

create table #temp_table (
        int_column int,
        varchar_column varchar(50)
    );

insert into #temp_table (int_column, varchar_column) values (case when isnumeric(replace('111----111', '-', '')) = 1 then replace('111----111', '-', '') end, 'string data 1');
insert into #temp_table (int_column, varchar_column) values (case when isnumeric(replace('text', '-', '')) = 1 then replace('text', '-', '') end, 'string data 2');
insert into #temp_table (int_column, varchar_column) values (try_convert(int, replace('258--', '-', '')), 'string data 3');
insert into #temp_table (int_column, varchar_column) values (try_convert(int, replace('123', '-', '')), 'string data 4');

select * from #temp_table;

/*
    |   int_column  |   varchar_column  |
    |   111111      |   string data 1   |
    |   NULL        |   string data 2   |
    |   258         |   string data 3   |
    |   123         |   string data 4   |
*/

I added some sample code as I would like to use it. I want to insert only the numeric value for a column if isnumeric() returns 1, otherwise insert a NULL value. I'm working on a SQL Server 2012. — Vladimir, Nov 14 '12 at 18:56

score 4 · Accepted Answer · answered Nov 14 '12 at 13:03

4

Perhaps:

SELECT value as actual_string
, replace(value, '-', '') as numeric_value
, CASE ISNUMERIC(replace(value, '-', ''))
  WHEN 1 THEN CAST(replace(value, '-', '') AS FLOAT)
  ELSE NULL END AS numeric_value_or_null
FROM TableName

Fiddle inside

answered Nov 14 '12 at 13:03

Tim Schmelter

450,073
74
686
939

score 3 · Answer 2 · answered Nov 14 '12 at 13:05

3

If you are on 2012 (select appropriate datatype for your data, I've assumed INT)

SELECT TRY_CONVERT(INT, replace(value, '-', ''))

answered Nov 14 '12 at 13:05

Martin Smith

438,706
87
741
845

score 1 · Answer 3 · answered Nov 14 '12 at 13:00

1

What's wrong with using a case statement? e.g.

declare @text varchar(50)
set @text = '747467'

select 
    case
        when isnumeric(@text) <> 1 then null
        else cast(@text as decimal)
    end as numeric_or_null

answered Nov 14 '12 at 13:00

adhocgeek

1,437
1
16
30

score 1 · Answer 4 · answered Nov 14 '12 at 13:02

select '14-154877-0' as actual_string, replace('14-154877-0', '-', '') as numeric_value, case when isnumeric(replace('14-154877-0', '-', ''))=1 then Cast(replace('14-154877-0', '-', '') as Numeric) else null end as numeric_value_or_null /* Here I wold like to return the numeric value instead of 1 */

Get null if non numeric or the actual numeric value in TSQL

4 Answers4