Removing leading spaces off an INT (SQL), TRIMS don't work

Question

I have a few rows of data which I need to check the following

1. if INT then produce the INT
2. if INT but with leading/trailing space, trim then produce INT
3. if not INT at all, then produce NULL

Here's the following data:

Number
514449
NA
NA
609924
609923
NA

the table above doesn't show it, but there's a leading space on the first row that I just can't get rid of using traditional TRIMS

I've tried a solution from this question but no luck either: LTRIM does not remove leading space in SQL

Here's the CASE statement I've built so far which doesn't correctly identify the leading space in the first row of data

    CASE  
        WHEN ISNUMERIC(TRIM([Number])) = 0 THEN 'NOT INT' 
        WHEN [Number] LIKE ' %' THEN 'Leading Space' 
        WHEN substring([Number],1,1) = ' ' THEN 'Leading Space' 
        ELSE [Number] 
    END AS 'CHECK'  

Answer 1

You can use TRY_CONVERT to attempt conversion to int, it will return NULL if it fails.

Your starting character is actually U+160 Non-Breaking Space. You can remove it using REPLACE

TRY_CONVERT(int, REPLACE(TRIM(YourValue), CHAR(160), ''))

In standard SQL Server you can also use TRIM FROM

TRY_CONVERT(int, TRIM(' ' + CHAR(160) FROM YourValue))