create table t1(
c1 nvarchar(32)
)
GO
insert into t1(c1) values('1'), ('11'), ('2'), ('3'),('A'), ('AA'), ('ABC'), (N'一'), (N'二'), (';'), (''''), ('B');
select * from t1 order by c1;
SELECT *, TRY_CONVERT(int, c1) from t1
order by TRY_CONVERT(int, c1);
SELECT *, TRY_CONVERT(int, c1) from t1
order by TRY_CONVERT(int, c1), c1;
SELECT *, TRY_CONVERT(int, c1) from t1
order by ISNULL(TRY_CONVERT(int, c1), 2147483647) , c1;
CREATE FUNCTION dbo.TryConvertInt
(
@value nvarchar(4000)
)
RETURNS int
AS
BEGIN
RETURN (SELECT CONVERT(int,
CASE WHEN LEN(@value) <= 11 THEN
CASE WHEN @value NOT LIKE N'%[^-0-9]%' THEN
CASE WHEN CONVERT(bigint, @value) BETWEEN -2147483648 AND 2147483647
THEN @value
END
END
END));
END
GO
SELECT *, dbo.TryConvertInt(c1) from t1
order by ISNULL(dbo.TryConvertInt( c1), 2147483647) , c1;