Cast as Text or Int based on data

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!

data .. issue ...

CASE WHEN mfgi.ITMREF_0 NOT LIKE '%[^0-9]%'
data could be like this '123 45' with spaces

which is going to the ELSE part
ELSE CAST (mfgi.ITMREF_0 AS INT) END'

but the data that is coming cannot be cast as int ...
'123 45' cannot be cast as int because of spaces
so error thrown

hope this explanation helps !!!!!! :slight_smile: :slight_smile:

1 Like

Thanks. As a noob, still don't know what to do about it. :face_with_raised_eyebrow:

If the data is a number, it will always be between 00000 and 99999 (5 digits). How can i cast that scenario as an INT and anything not in that scenario as TEXT?

The logic is backwards. When the condition is true, the data is numbers only. I used "varchar(max)" because you really shouldn't be using "text" at this point, but naturally you can change it back to "text" if you really need to.

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

1 Like

Could you please explain why you want to do these conversions? What is the business use case here?

Sure- Once the data comes into Excel, if the numbers come in as text, then VLookups don't work correctly.

So to me the issue is in Excel. why not fix the issue in Excel itself and not in the SQL Query? Because tomorrow this will most probably break again with some other data issue and you will have to come back in and fix it.

All tools have their sharp edges. Depends how you wield them!

'CASE WHEN mfgi.ITMREF_0 LIKE '[0-9][0-9][0-9][0-9][0-9]' AND LEN(mfgi.ITMREF) = 5 THEN CAST (mfgi.ITMREF_0 AS int ) ELSE CAST (mfgi.ITMREF_0 AS varchar(max) ) END'

Scott- Thanks for the input. I'm now getting a new error-
"Conversion failed when converting the varchar value 'ACC-DP-SRT-SAMP-INSP' to data type int.

With the logic you provided, I'm not understanding why it is trying to make the text INT...?

If i reverse your logic to CASE WHEN mfgi.ITMREF_0 LIKE '%[^0-9]%' THEN CAST (mfgi.ITMREF_0 AS int) ELSE CAST (mfgi.ITMREF_0 AS varchar(max)) END

Then I get the Error "Conversion failed when converting the nvarchar value 'ACC-DP-SRT-SAMP-INSP' to data type int"

To Yosiasz's point, I can correct this in Excel, but wanting to learn SQL, why not try to fix it there.

Do you really want to cast to int and get the following results?

You keep changing the code I posted. Your original code had the logic backwards.

The condition " column_name NOT LIKE '%[^0-9]%' " is true when the column is numeric. If you drop the NOT from the NOT LIKE, you reverse the meaning.

However, if you are interested in only 5 digit values, you can use the code I posted later:

'CASE WHEN mfgi.ITMREF_0 LIKE '[0-9][0-9][0-9][0-9][0-9]' AND LEN(mfgi.ITMREF) = 5 THEN CAST (mfgi.ITMREF_0 AS int ) ELSE CAST (mfgi.ITMREF_0 AS varchar(max) ) END'

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

will never work: a column has one and only one data type: INT exclusive or CHAR. Not sometimes an INT and sometimes a CHAR, depending on what record it is in.

The error you got

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.

Is SQL Server complaining about squeezing two different data types into one column.

Besides, mfgi.ITMREF_0 is already a string, what is the point of converting a string to a string?

Yeah, Excel has its quirks. Best solution I can think of is making two columns in the result set. Something in the line of:

	,CASE 
		WHEN isNumeric(mfgi.ITMREF_0) = 1
			THEN CAST(mfgi.ITMREF_0 AS INT)
		ELSE NULL
	END AS ITMREF_0_int
	,CASE 
		WHEN isNumeric(mfgi.ITMREF_0) = 0
			THEN mfgi.ITMREF_0
		ELSE NULL
	END AS ITMREF_0_text

Thank you.

This topic can be considered resolved.