Why is my conversion from varchar to bigint not working?

Question

I am trying to convert from varchar to bigint, but keep getting

Error converting data type varchar to bigint.

This is my syntax:

CASE SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) 
WHEN '-' THEN NULL 
ELSE CONVERT(bigint, SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr))) 
END

This is part of the json value im taking the substring from:

{
         "lat": "52.650000",
         "lon": "5.730000",
         "name": "NAGELE",
         "cmt": "PROV: FLEVOLAND, INW: -",
         "desc": "PROV: FLEVOLAND, INW: -",
         "sym": "Medium City"
      },
      {
         "lat": "52.670000",
         "lon": "5.600000",
         "name": "URK",
         "cmt": "PROV: FLEVOLAND, INW: 16489",
         "desc": "PROV: FLEVOLAND, INW: 16489",
         "sym": "Medium City"
      },

So when INW: is '-', I want to insert null when INW: is for example 16489 I want to insert this value (column is a bigint)

I have also tried it like this:

CASE SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) 
WHEN '-' THEN NULL 
ELSE CAST(SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) AS bigint) 
END

This is my loop for the JSON objects:

begin
            declare @_StateProvinceID int = (NEXT VALUE FOR [Sequences].[StateProvinceID])
            Insert into [Application].[StateProvinces] (StateProvinceID,StateProvinceCode,StateProvinceName,CountryID,SalesTerritory)
            values(@_StateProvinceID,UPPER(SUBSTRING (@StateProvince, 1, 2)),@StateProvince,@_CountryID,@_SalesTerritory)

            declare @_InnerJson VARCHAR(MAX) = @json
            DECLARE @wpt VARCHAR(MAX);
            SET @wpt = JSON_QUERY(@_InnerJson, '$.wpt');

            ;with cte_a as (
            SELECT UPPER(SUBSTRING (@StateProvince, 1, 2))[StateProvinceCode],@StateProvince [StateProvince],* FROM  
            OPENJSON ( @wpt )  
            WITH (name varchar(max) '$.name',lat varchar(max) '$.lat',lon varchar(max) '$.lon', descr varchar(max) '$.desc'))

            Insert Into [Application].[Cities] (StateProvinceID,CityName,Location,LatestRecordedPopulation)
            select @_StateProvinceID,a.name,geography::Point(lat, lon, 4326),
                (CASE (SELECT SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr))) WHEN '-' THEN NULL ELSE (SELECT SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr))) END)
            from cte_a a
        End

Does anyone have an idea how to get this working?

Answer 1

If you're positive that this bit works...

SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) 

Then wrap it in this...

CAST(
    NULLIF(
        SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)),
        '-'
    )
    AS BIGINT
)

Just be very certain that your original snippet always gives what you want first. Especially that it never gives unexpected spaces, etc.

Edit:

As this isn't working I can only conclude that your substring is Not always returning what you expect.

Please show both the input strings (descr) and the results of the following... (As an edit to your question.)

'>' + SUBSTRING(descr, CHARINDEX('INW: ', descr) + 5, LEN(descr)) + '<'