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:

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:

CodePudding user response：

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))