Cast as int in CASE expression in SQL

Question

I have a query that is pulling data from 10 tables and a sub-query. One of the selects is for a location and I am using a case expression to clean up the data on output. The field it is pulling the data from is varchar, and the data can be values such as: USA800, admin, ccc-ulw, ccc-ury, 002-Carson, 066-Nellis BX, 042-Junction City (there are more).

I want the case express to report back a specific value if it one of the locations that don't start with a number, but if it is a number, I want it to return just the number, as an int (dropping the 0's).

Here is the query as I have it now (the entire SQL may not be needed, but I figured it would help context):

    select 
    ct.id as CaseID
    ,c.first_name as FirstName
    ,c.last_name as LastName
    ,c.id as CustomerID
    ,SUBSTRING(sdu.User_ID,CHARINDEX('-',sdu.User_ID)+1,(((LEN(sdu.User_ID))-CHARINDEX('-', REVERSE(sdu.User_ID)))-CHARINDEX('-',sdu.User_ID))) as [Username from Navigator]
    ,case sdu.Application_Name when 'Navigator' then sn.[RESP CODE] else '' end as [Responsibility Code from Navigator]
    ,case sdu.Application_Name when 'Navigator' then RIGHT(sn.[EMUL STATION], 4) else '' end as [CStat From Navigator]
    ,ce02.id as JobCode
    ,case l.id 
        when 'USA800' then 'USA800' 
        when 'admin' then 'Admin'
        when 'ccc-ulw' then 'CCC-Lawrence'
        when 'ccc-ury' then 'CCC-Raytown'
        when 'ccc-usj' then 'CCC-St Joseph'
        when 'ccc-uwf' then 'CCC-Wichita Falls'
        else cast(SUBSTRING(l.id, 0, charindex('-', l.id, 0)) as int) 
    end as location
    ,l.id
    ,c2.first_name as SubmittedByFirstName
    ,c2.last_name as SubmittedByLastName
    ,sn.INST as Institution
    ,case sdupc.Application_Name when 'PartnerCare' then sdupc.User_ID else '' end as [Partnercare Username]
from Case_Table ct 
    left join Case_Type t on t.case_type_pk = ct.case_type_pk
    left join Case_Category cc on cc.case_category_pk = ct.case_category_pk
    left join (select max(cas.case_pk) as case_pk, customer_pk from Case_Table cas join Case_Type cat on cas.case_type_pk=cat.case_type_pk where cat.id = 'ASR' group by cas.customer_pk) ce01 on ce01.case_pk = ct.case_pk
    left join Custom_Entity02 ce02 on ce02.custom_entity02_pk = ct.Virtual_JobCode
    left join Customer c on ce01.customer_pk = c.customer_pk
    left join Location l on l.location_pk = c.location_pk
    right join Customer c2 on c2.customer_pk = ct.installed_by_customer_pk
    left join Prod.dbo.Staging_DataFeed_Users sdu on sdu.User_Name = c.description and sdu.application_name = 'Navigator'
    left join Prod.dbo.Staging_DataFeed_Users sdupc on sdupc.User_Name = c.description and sdupc.application_name = 'PartnerCare'
    left join Prod.dbo.Staging_Navigator sn on sn.NAME = c.description and sn.NAME = sdu.User_Name
where t.id='ASR'

When running the query, I get a conversion failed error when converting the varchar value to data type int.

Either I am missing something, or there is a better way to do it and I am open to ideas.

The customer is expecting integer in this case (they want it to look like an integer without the leading zeros).

The results of this query are used in a foxtrot script for de-provisioning users from applications and those numbers represent branches in our bank system. The leading zeroes cause issues. If the result is a string, it is ignored.

Answer 1

Why are you casting the substring as an int in the first place? The consumer is going to expect that column to output as a string in every other case. So my suggestion: Just remove the cast.

If you only want a value to appear in the resultset when it is a valid int, and you're on < 2012 where TRY_CAST() is not possible, then you can say:

else case when isnumeric(SUBSTRING(l.id, 0, charindex('-', l.id, 0))) = 1
  THEN CAST SUBSTRING(l.id, 0, charindex('-', l.id, 0)) AS int END

Note: ISNUMERIC() is not a perfect validation that it is specifically an integer, but is probably fine here, since you don't really need an int, just a string that is formatted like an int.

Update What you can do now that I understand the requirements better is convert to an int and then back to a string. Since you are on 2014 you can use TRY_CONVERT():

ELSE CONVERT(varchar(12), 
  TRY_CONVERT(int, SUBSTRING(l.id, 0, charindex('-', l.id, 0)))) 
END

You might also consider fixing the design so that you don't have to parse these important bits of information out of bigger pieces of information, or at least move your CASE expression to a computed column or a view so you don't have to put all that logic in all (or any!) of your queries.

Answer 2

If you're on SQL Server 2012 or newer, you can substitute your CAST() function for the TRY_CAST() function.

The difference between CAST() and TRY_CAST() is that the former will fail when a value cannot be converted, whereas the latter will just return a NULL value. The NULL value may not be what you want, so you should test this change carefully so you don't break anything.