Convert from a table with inconsistent data

• Douwe

  SSC Enthusiast

  Points: 101

  More actions

  May 5, 2005 at 1:10 am

  #166754

  Hi,

  I want to use a between statement to get data from a varchar field where Referance numbers is stored. Most of the varchars are numbers, but some of them has an 'AF' in front. I used the statements:

  select refno from SABPP.SABPP_CRMCLT Where refno not in ('A%') AND RefNo Between 10 AND 100 ORDER BY REFNO

  then I tried to insert the values in a temp table

  Create Table #Temp(

  RefNo int NOT NULL

  )

  Insert Into #Temp(RefNo)

  select refno from Table Where refno not in ('A%')

  But still I get the error: cannot convert the value 'AF01' to int...

  Help...

  Anyone..?

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  May 5, 2005 at 4:59 am

  #557013

  Have you tried explicit conversion to int before storing/retrieving ?!

  eg: select cast(left(refno, 2) as integer) ?!

  1) Identify all rows where refno NOT LIKE 'A%'

  2) Convert remaining rows to int value

  3) Insert into temp table

  4) Query

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• ron k

  SSCertifiable

  Points: 6103

  More actions

  May 5, 2005 at 9:24 am

  #557106

  select refno from SABPP.SABPP_CRMCLT

  Where refno not LIKE 'A%' ....

   

• sara karasik

  SSCommitted

  Points: 1573

  More actions

  May 19, 2005 at 10:46 am

  #559925

  Use the isnumeric function, to select only those reference numbers that can be converted to numbers.

   

  Insert Into #Temp(RefNo)

  select refno from Table

  where isnumeric(refno) = 1