Value returned if cast fails or is not supported. The data type into which to cast expression. | ReturnValueIfErrorCast | SQL_VARIANT = NULL |
() RETURNS INT AS BEGIN RETURN 0 END'ĭbo.TRY_CAST(Expression, Data_Type, ReturnValueIfErrorCast) IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'.')) dbo.TRY_CAST(Expression, Data_Type, ReturnValueIfErrorCast)Įxample: SELECT CASE WHEN dbo.TRY_CAST('6666666166666212', 'INT', DEFAULT) IS NULLįor now only supports the data types INT, DATE, NUMERIC, BIT and FLOAT However, it is still very useful because it allows you to return aĭefault value if CAST is not performed correctly. Must additionally perform an explicit CONVERT or CAST to the field. The two main differences are that you must pass 3 parameters and you You can see it in the next link below and we help each other to improve it. I wrote a useful scalar function to simulate the TRY_CAST function of SQL Server 2012 in SQL Server 2008. However, it also gives NULL for valid integers whose length exceeds 10 characters due to leading zeros. It checks that your string does not contain any non-digit characters, is not empty, and does not overflow (by exceeding the maximum value for the int type). The snippet below works on non-negative integers. Withdrawn: The approach below is no longer recommended, as is left just for reference. Increasing the parameter length would result in errors on numbers that overflow BIGINT, such as BBANs (basic bank account numbers) like '212110090000000235698741'. Joseph's answer can lead to incorrect results due to silent truncations of the argument '00000000000000001234' evaluates to 12.Since it does not use local variables, my function can be defined as an inline table-valued function, allowing for better query optimization.or, in order to mimic the behaviour of native INT conversions. My logic does not tolerate occurrences of.Results are similar to Joseph Sturtevant's answer, with the following main differences: The logic can be defined as an inline table-valued function: CREATE FUNCTION Tr圜onvertInt NVARCHAR(MAX))ĬASE WHEN CONVERT(FLOAT, BETWEEN -21474836483647 This approach is based on adrianm's comment. Update: My new recommendation would be to use an intermediary test conversion to FLOAT to validate the number.
#Sql convert string to int code
If you want a reliable conversion, you'll need to code one yourself. SELECT CASE WHEN ISNUMERIC('1,300') = 1 THEN CAST('1,300' AS INT) END SELECT CASE WHEN ISNUMERIC('4.4') = 1 THEN CAST('4.4' AS INT) END SELECT CASE WHEN ISNUMERIC('$') = 1 THEN CAST('$' AS INT) END SELECT CASE WHEN ISNUMERIC('-') = 1 THEN CAST('-' AS INT) END SELECT Value, dbo.Tr圜onvertInt(Value) FROM I used this page extensively when creating my solution.Īs has been mentioned, you may run into several issues if you use ISNUMERIC: - Incorrectly gives 0: WHEN CHARINDEX('.', > 0 THEN CONVERT(bigint, 2))ĮLSE CONVERT(bigint, > 2147483647 RETURN NULLĭECLARE TABLE(Value nvarchar(50)) - Result IF ( CHARINDEX('.', > 0 AND CONVERT(bigint, 1)) 0 ) RETURN NULL This will avoid the issues that Fedor Hajdu mentioned with regards to currency, fractional numbers, etc: CREATE FUNCTION varchar(18)) If you are on SQL Server 2005, 2008, or 2008 R2:Ĭreate a user defined function.
If you are on SQL Server 2012 (or newer):
0 kommentar(er)
