How to exclude text values and only include numbers in a varchar column

• NineIron

  SSChampion

  Points: 12537

  More actions

  October 3, 2016 at 4:55 am

  #330973

  I have a table with Lab values in it. The lab lest results can either be a number or a comment such as "Unable to perform". Is there a function that I can use to pull only the numbers? IsNumeric doesn't seem to work.

• Eirikur Eiriksson

  SSC Guru

  Points: 182920

  More actions

  October 3, 2016 at 5:02 am

  #1904362

  NineIron (10/3/2016)

  ---

  I have a table with Lab values in it. The lab lest results can either be a number or a comment such as "Unable to perform". Is there a function that I can use to pull only the numbers? IsNumeric doesn't seem to work.

  Can you please post some examples and the expected results?

  😎

  If you are looking for cleaning mixed input then have a look here.[/url]

• NineIron

  SSChampion

  Points: 12537

  More actions

  October 3, 2016 at 5:02 am

  #1904363

  create table #Test

  (

  ResultValue varchar(30)

  )

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('110.1')

  insert into #Test(ResultValue) values('90.9')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('Unable to Calculate')

  insert into #Test(ResultValue) values('Unable to Calculate')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('35.7')

  insert into #Test(ResultValue) values('2')

  insert into #Test(ResultValue) values('79.5')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('96.3')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('70.5')

  insert into #Test(ResultValue) values('85.5')

  insert into #Test(ResultValue) values('76.5')

  insert into #Test(ResultValue) values('43.3')

  insert into #Test(ResultValue) values('92.7')

  insert into #Test(ResultValue) values('96.9')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('80')

  insert into #Test(ResultValue) values('0.6')

  insert into #Test(ResultValue) values('107')

  insert into #Test(ResultValue) values('129.7')

  insert into #Test(ResultValue) values('66.2')

  insert into #Test(ResultValue) values('80.2')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('84.1')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('69.3')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('85.8')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('51.1')

  insert into #Test(ResultValue) values('61.9')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('98.2')

  insert into #Test(ResultValue) values('70.8')

  insert into #Test(ResultValue) values('81')

  insert into #Test(ResultValue) values('91.2')

  insert into #Test(ResultValue) values('110.5')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('78.1')

  insert into #Test(ResultValue) values('94.6')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('61.8')

  insert into #Test(ResultValue) values('74.9')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('80.5')

  insert into #Test(ResultValue) values('97.6')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('91.6')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('75.6')

  insert into #Test(ResultValue) values('66.2')

  insert into #Test(ResultValue) values('80.2')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('102.9')

  insert into #Test(ResultValue) values('84.9')

  insert into #Test(ResultValue) values('0.7')

  insert into #Test(ResultValue) values('93.8')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('77.4')

  insert into #Test(ResultValue) values('90.4')

  insert into #Test(ResultValue) values('109.5')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('60.1')

  insert into #Test(ResultValue) values('72.9')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('64.8')

  insert into #Test(ResultValue) values('78.5')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('75.6')

  insert into #Test(ResultValue) values('91.6')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('69.8')

  insert into #Test(ResultValue) values('94.3')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('106')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('68.5')

  insert into #Test(ResultValue) values('56.6')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('77.8')

  insert into #Test(ResultValue) values('70')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('54.2')

  insert into #Test(ResultValue) values('57.6')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('57.8')

  insert into #Test(ResultValue) values('65.7')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('128.5')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('77.8')

  insert into #Test(ResultValue) values('94.3')

  insert into #Test(ResultValue) values('105.1')

  insert into #Test(ResultValue) values('86.8')

  insert into #Test(ResultValue) values('96.3')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('79.5')

  insert into #Test(ResultValue) values('1.6')

  insert into #Test(ResultValue) values('0.5')

  insert into #Test(ResultValue) values('45.3')

  insert into #Test(ResultValue) values('54.9')

  insert into #Test(ResultValue) values('161.6')

  insert into #Test(ResultValue) values('133.8')

  insert into #Test(ResultValue) values('162.1')

  insert into #Test(ResultValue) values('0.5')

  insert into #Test(ResultValue) values('133.3')

  insert into #Test(ResultValue) values('81.5')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('67.2')

  insert into #Test(ResultValue) values('53')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('64.2')

  insert into #Test(ResultValue) values('111.1')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('72.4')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('59.7')

  insert into #Test(ResultValue) values('91.7')

  insert into #Test(ResultValue) values('0.7')

  insert into #Test(ResultValue) values('1.3')

  insert into #Test(ResultValue) values('98.5')

  insert into #Test(ResultValue) values('81.3')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('71.6')

  insert into #Test(ResultValue) values('86.8')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('64.2')

  insert into #Test(ResultValue) values('77.8')

  insert into #Test(ResultValue) values('96')

  insert into #Test(ResultValue) values('44')

  insert into #Test(ResultValue) values('53.3')

  insert into #Test(ResultValue) values('80.1')

  insert into #Test(ResultValue) values('79.2')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('66.1')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('77.1')

  insert into #Test(ResultValue) values('63.6')

  insert into #Test(ResultValue) values('53')

  insert into #Test(ResultValue) values('64.2')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('72.3')

  insert into #Test(ResultValue) values('87.7')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('107.8')

  insert into #Test(ResultValue) values('88.9')

  insert into #Test(ResultValue) values('104.6')

  insert into #Test(ResultValue) values('86.3')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('88.3')

  insert into #Test(ResultValue) values('93.5')

  insert into #Test(ResultValue) values('107.1')

  insert into #Test(ResultValue) values('77.1')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('78.3')

  insert into #Test(ResultValue) values('94.9')

  insert into #Test(ResultValue) values('66.4')

  insert into #Test(ResultValue) values('94.9')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('76.7')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('78.3')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('63.1')

  insert into #Test(ResultValue) values('76.5')

  insert into #Test(ResultValue) values('80.5')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('Unable to Calculate')

  insert into #Test(ResultValue) values('Unable to Calculate')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('63.2')

• DesNorton

  SSC-Insane

  Points: 24424

  More actions

  October 3, 2016 at 6:48 am

  #1904380

  Based on the data that you have supplied, the following SQL works

  SELECT *

  FROM #Test AS t

  WHERE ISNUMERIC(ResultValue) = 1;

  ---

  How to post data/code on a forum to get the best help.

• NineIron

  SSChampion

  Points: 12537

  More actions

  October 3, 2016 at 6:54 am

  #1904385

  Thanx.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 3, 2016 at 7:12 am

  #1904388

  Careful, though. Add these rows to your table and try again. I'm sure there are other values that might trip you up, as well.

  insert into #Test(ResultValue) values('3E+1')

  insert into #Test(ResultValue) values('.')

  insert into #Test(ResultValue) values('+')

  insert into #Test(ResultValue) values('-')

  John

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 3, 2016 at 12:35 pm

  #1904516

  Based on this:

  create table #Test

  (

  ResultValue varchar(30)

  )

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('110.1')

  insert into #Test(ResultValue) values('90.9')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('Unable to Calculate')

  insert into #Test(ResultValue) values('Unable to Calculate')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('35.7')

  insert into #Test(ResultValue) values('2')

  insert into #Test(ResultValue) values('79.5')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('96.3')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('70.5')

  insert into #Test(ResultValue) values('85.5')

  insert into #Test(ResultValue) values('76.5')

  insert into #Test(ResultValue) values('43.3')

  insert into #Test(ResultValue) values('92.7')

  insert into #Test(ResultValue) values('96.9')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('80')

  insert into #Test(ResultValue) values('0.6')

  insert into #Test(ResultValue) values('107')

  insert into #Test(ResultValue) values('129.7')

  insert into #Test(ResultValue) values('66.2')

  insert into #Test(ResultValue) values('80.2')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('84.1')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('69.3')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('85.8')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('51.1')

  insert into #Test(ResultValue) values('61.9')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('98.2')

  insert into #Test(ResultValue) values('70.8')

  insert into #Test(ResultValue) values('81')

  insert into #Test(ResultValue) values('91.2')

  insert into #Test(ResultValue) values('110.5')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('78.1')

  insert into #Test(ResultValue) values('94.6')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('61.8')

  insert into #Test(ResultValue) values('74.9')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('80.5')

  insert into #Test(ResultValue) values('97.6')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('91.6')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('75.6')

  insert into #Test(ResultValue) values('66.2')

  insert into #Test(ResultValue) values('80.2')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('102.9')

  insert into #Test(ResultValue) values('84.9')

  insert into #Test(ResultValue) values('0.7')

  insert into #Test(ResultValue) values('93.8')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('77.4')

  insert into #Test(ResultValue) values('90.4')

  insert into #Test(ResultValue) values('109.5')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('60.1')

  insert into #Test(ResultValue) values('72.9')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('64.8')

  insert into #Test(ResultValue) values('78.5')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('75.6')

  insert into #Test(ResultValue) values('91.6')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('69.8')

  insert into #Test(ResultValue) values('94.3')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('106')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('68.5')

  insert into #Test(ResultValue) values('56.6')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('77.8')

  insert into #Test(ResultValue) values('70')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('54.2')

  insert into #Test(ResultValue) values('57.6')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('57.8')

  insert into #Test(ResultValue) values('65.7')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('128.5')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('77.8')

  insert into #Test(ResultValue) values('94.3')

  insert into #Test(ResultValue) values('105.1')

  insert into #Test(ResultValue) values('86.8')

  insert into #Test(ResultValue) values('96.3')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('79.5')

  insert into #Test(ResultValue) values('1.6')

  insert into #Test(ResultValue) values('0.5')

  insert into #Test(ResultValue) values('45.3')

  insert into #Test(ResultValue) values('54.9')

  insert into #Test(ResultValue) values('161.6')

  insert into #Test(ResultValue) values('133.8')

  insert into #Test(ResultValue) values('162.1')

  insert into #Test(ResultValue) values('0.5')

  insert into #Test(ResultValue) values('133.3')

  insert into #Test(ResultValue) values('81.5')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('67.2')

  insert into #Test(ResultValue) values('53')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('64.2')

  insert into #Test(ResultValue) values('111.1')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('72.4')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('59.7')

  insert into #Test(ResultValue) values('91.7')

  insert into #Test(ResultValue) values('0.7')

  insert into #Test(ResultValue) values('1.3')

  insert into #Test(ResultValue) values('98.5')

  insert into #Test(ResultValue) values('81.3')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('71.6')

  insert into #Test(ResultValue) values('86.8')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('64.2')

  insert into #Test(ResultValue) values('77.8')

  insert into #Test(ResultValue) values('96')

  insert into #Test(ResultValue) values('44')

  insert into #Test(ResultValue) values('53.3')

  insert into #Test(ResultValue) values('80.1')

  insert into #Test(ResultValue) values('79.2')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('66.1')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('77.1')

  insert into #Test(ResultValue) values('63.6')

  insert into #Test(ResultValue) values('53')

  insert into #Test(ResultValue) values('64.2')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('72.3')

  insert into #Test(ResultValue) values('87.7')

  insert into #Test(ResultValue) values('1.1')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('107.8')

  insert into #Test(ResultValue) values('88.9')

  insert into #Test(ResultValue) values('104.6')

  insert into #Test(ResultValue) values('86.3')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('88.3')

  insert into #Test(ResultValue) values('93.5')

  insert into #Test(ResultValue) values('107.1')

  insert into #Test(ResultValue) values('77.1')

  insert into #Test(ResultValue) values('0.8')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('78.3')

  insert into #Test(ResultValue) values('94.9')

  insert into #Test(ResultValue) values('66.4')

  insert into #Test(ResultValue) values('94.9')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('76.7')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('78.3')

  insert into #Test(ResultValue) values('1')

  insert into #Test(ResultValue) values('63.1')

  insert into #Test(ResultValue) values('76.5')

  insert into #Test(ResultValue) values('80.5')

  insert into #Test(ResultValue) values('0.9')

  insert into #Test(ResultValue) values('Unable to Calculate')

  insert into #Test(ResultValue) values('Unable to Calculate')

  insert into #Test(ResultValue) values('1.2')

  insert into #Test(ResultValue) values('63.2');

  Then this:

  select * from #Test where ResultValue not like '%[^0-9.]%';

• NineIron

  SSChampion

  Points: 12537

  More actions

  October 4, 2016 at 3:59 am

  #1904617

  Thanx.