Databases
StackOverflow2013
Postsdbo
POGet null if non numeric or the actual numeric value in TSQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGet null if non numeric or the actual numeric value in TSQL
    text
    copied!<p>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?</p> <pre><code>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 */ </code></pre> <p><strong>Sample data</strong></p> <p>The insert statements are a result of the excel concatenation function.</p> <p>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?</p> <pre><code>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 | */ </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload