SQL query: how to convert data to numbers only when it does not contains letters

Question

While extracting ERP products' references from SQL server, it is extracted as text. These references sometimes contains only numbers, and sometimes it contains numbers + the letters CN. I would like to extract it as a number when it only contains numbers, and as a text if it contains the letters CN. Unfortunately, I did not find a way to do it.

This is what I tried ("INVMB.MB110" being the the field "Reference" in the SQL server):

SELECT
CASE 
WHEN INVMB.MB110 LIKE '%CN%' 
THEN INVMB.MB110 
ELSE CAST(INVMB.MB110 AS INT) END 
AS 'Reference'

I was expecting to convert to integer only if the reference does not contain 'CN', but it seems it tries to convert it in any case. Here is the error message I get:

Failed converting varchar value '30903CN013 ' to data type int.

Answer 1

You can create two columns one for the string data and one for the numeric data. Assuming you have SQL Server 2016 or higher you can use the TRY_CONVERT or TRY_PARSE function.

SELECT
    [INVMB.MB110_INT] = TRY_CONVERT(INT, [INVMB.MB110])
    ,[INVMB.MB110_STRING] = [INVMB.MB110]
    ,[IsNumberDataType] = CASE WHEN TRY_CONVERT(INT, [INVMB.MB110]) IS NULL THEN 0 ELSE 1 END

The TRY_CONVERT function will convert the string to a numeric if it is possible otherwise it will return NULL.

Note that the TRY_CONVERT function will return a numeric value of 0 if the source string is an empty string. If you don’t want 0 to be retuned for an empty string, the use the TRY_PARSE function.

If you absolutely require the values in a single column, you can convert the values to a SQL_VARIANT types (after you converted the string to the desired base type). I personally avoid SQL_VARIANT types like the plague, there is just too many performance and implicit conversion issues associated with them. I have included an example of a query that returns some sample data.

WITH VariantSampleData
AS
(
    SELECT 
        variant_value = TRY_CONVERT(SQL_VARIANT, TRY_CONVERT(INT, '123') )
    UNION ALL
    SELECT
        variant_value = TRY_CONVERT(SQL_VARIANT, 'abc' )
)
SELECT
    variant_value
    ,BaseType = SQL_VARIANT_PROPERTY(variant_value,'BaseType')
    ,[math] = TRY_CONVERT(INT, variant_value) + 1
FROM
    VariantSampleData