Cast as Text or Int based on data

[Remotruker]

Total Noob here and trying to modify a bit of SQL as my IT is working on bigger fish so i thought i'd give it a go-

I have a list of data which used to just contain numbers, and now contains text(with dashes "-") and numbers. What i want to happen is if the data is text then cast as text, if data is numeric then cast as Int. The list of data is one or the other- Numbers or Text with dashes.

 

Full SQL: 

    stSQL = "SELECT rou.WST_0, " & _
    "CASE WHEN itm.ALG_0 is NULL THEN 'n/a' WHEN itm.ALG_0 = 'Z' THEN 'n/a' WHEN itm.ALG_0 = '' THEN 'n/a' ELSE itm.ALG_0 END AS [Allergen], " & _
    "mfgh.MFGNUM_0, CASE WHEN mfgi.ITMREF_0 NOT LIKE '%[^0-9]%' THEN CAST (mfgi.ITMREF_0 AS TEXT) ELSE CAST (mfgi.ITMREF_0 AS INT) END, itm.TCLCOD_0, " & _
    "CASE WHEN itm.TSICOD_1 = 'NA' THEN itm.ITMDES1_0 ELSE 'Organic' + ' ' + itm.ITMDES1_0 END as [Description],'',  " & _
    "rou.WSTNBR_0, mfgi.EXTQTY_0, mfgh.STRDAT_0, mfgh.ENDDAT_0, " & _
    "(mfgo.EXTSETTIM_0 + mfgo.EXTOPETIM_0)/60 as [Hours], " & _
    "CASE WHEN itm.ZITMEIGHTY_0 = 2 THEN '80/20' ELSE '' END as [EightyTwentyItem], " & _
    "CASE WHEN rout.ZLASTTSUP_0 = '" & Format("1/1/1753") & "' THEN NULL ELSE rout.ZLASTTSUP_0 END [LastTimeStudyDate], " & _
    "rou.OPENUM_0, " & _
    "REPLACE((CASE WHEN itm.ZCASEQTY_0 = 0 THEN '' ELSE Str(itm.ZCASEQTY_0, 8,0) END), ' ', '') As [CaseQuantity], " & _
    "itm.ZUNITQTY_0, a.Message, '', '', zsd.ZERODAT_0 [PSO Date] " & _
    "FROM GLORYBEE.MFGHEAD mfgh " & _
    "INNER JOIN GLORYBEE.MFGOPE mfgo ON mfgh.MFGNUM_0 = mfgo.MFGNUM_0 " & _
    "INNER JOIN GLORYBEE.MFGITM mfgi ON mfgh.MFGNUM_0 = mfgi.MFGNUM_0 " & _
    "INNER JOIN GLORYBEE.ITMMASTER itm ON mfgi.ITMREF_0 = itm.ITMREF_0 " & _
    "LEFT OUTER JOIN GLORYBEE.ROUTING rout ON mfgi.ITMREF_0 = rout.ITMREF_0 and mfgh.ROUALT_0 = rout.ROUALT_0 and mfgh.MFGFCY_0 = rout.FCY_0 " & _
    "LEFT OUTER JOIN GLORYBEE.ROUOPE rou ON mfgo.MFGFCY_0 = rou.FCY_0 and mfgi.ITMREF_0 = rou.ITMREF_0 and rout.ROUALT_0 = rou.ROUALT_0 and mfgo.OPENUM_0 = rou.OPENUM_0 and mfgo.RPLIND_0 = rou.RPLIND_0 " & _
    "LEFT OUTER JOIN dbo.ZeroStockDate zsd ON mfgi.ITMREF_0 = zsd.ITMREF_0 " & _
    "INNER JOIN GLORYBEE.APLSTD_7003 a ON itm.ZUMDROP_0 = a.ID " & _
    "AND mfgh.STRDAT_0 >= '" & Format(Startdate, "yyyy-mm-dd") & "' AND mfgh.STRDAT_0 <= '" & Format(EndDate, "yyyy-mm-dd") & "' " & _
    "AND (mfgh.MFGTRKFLG_0 = '1' or mfgh.MFGTRKFLG_0 = '2' or mfgh.MFGTRKFLG_0 = '3' or mfgh.MFGTRKFLG_0 = '4') AND (mfgh.MFGSTA_0 = '1' or mfgh.MFGSTA_0 = '2') and (itm.ZPRODDEPT_0 = 1 OR itm.ZPRODDEPT_0 = 2) " & _
    "ORDER BY mfgh.MFGNUM_0, rou.OPENUM_0 " & _
    "option(recompile) "

 

My trouble is in the 3rd line:

CASE WHEN mfgi.ITMREF_0 NOT LIKE '%[^0-9]%' THEN CAST (mfgi.ITMREF_0 AS TEXT) ELSE CAST (mfgi.ITMREF_0 AS INT) END

This SQL is embedded in an Excel Macro and when I try to run this bit, i get an Operand type dash: text is incompatible with int.

 

Thanks for the help!