Conversion failed when converting the nvarchar value 'H14' to data type int

Question

I realize that there are some similar questions out there, and I realize that the H is what is causing the problem. The first part of my question is how the letter got into a column of years (I'm not sure why the year column is the data type nvarchar).

Here is my code

<cfset StartYear=Val(DateFormat(Now(),'yyyy'))-10>
<cfquery name="RegYearList" datasource="Binkley">
    Select Distinct YEAR
    From RegForm200
    Where Year Is Not NULL
    And rTrim(YEAR)>''
    And Cast(Year As Int)>=#StartYear#
    And Cast(Year As Int)<=#Year(Now())#
    Order By Year Desc
</cfquery>

Any help would be greatly appreciated. I just inherited this database and program in June of 2019 and I am still trying to find my way.

Answer 1

Try this:

and cast(SUBSTRING('H14', PATINDEX('%[0-9]%', 'H14'), LEN('H14')) as int)