TSQL - Cast string to integer or return default value

Question

Is there a way in T-SQL to cast an nvarchar to int and return a default value or NULL if the conversion fails

User · Answer

My solution to this issue was to create the function shown below  My requirements included that the number had to be a standard integer  not a BIGINT  and I needed to allow negative numbers and positive numbers  I have not found a circumstance where this fails   CREATE FUNCTION  dbo   udfIsInteger        -- Add the parameters for the function here      Value nvarchar max    RETURNS int AS BEGIN     -- Declare the return variable here     DECLARE  Result int   0      -- Add the T-SQL statements to compute the return value here     DECLARE  MinValue nvarchar 11     -2147483648      DECLARE  MaxValue nvarchar 10     2147483647       SET  Value   ISNULL  Value          IF LEN  Value  0 OR        ISNUMERIC  Value  lt  gt 1 OR        LEFT  Value 1   -  AND LEN  Value  gt 11  OR        LEFT  Value 1   -  AND LEN  Value  11 AND  Value gt  MinValue  OR        LEFT  Value 1  lt  gt  -  AND LEN  Value  gt 10  OR        LEFT  Value 1  lt  gt  -  AND LEN  Value  10 AND  Value gt  MaxValue        GOTO FINISHED      DECLARE  cnt int   0     WHILE  cnt lt LEN  Value      BEGIN       SET  cnt  cnt 1       IF SUBSTRING  Value  cnt 1  NOT IN   -   0   1   2   3   4   5   6   7   8   9   GOTO FINISHED     END     SET  Result 1  FINISHED      -- Return the result of the function     RETURN  Result  END

User · Answer

As has been mentioned  you may run into several issues if you use ISNUMERIC   -- Incorrectly gives 0  SELECT CASE WHEN ISNUMERIC  -     1 THEN CAST  -  AS INT  END     -- Error  conversion failure   SELECT CASE WHEN ISNUMERIC        1 THEN CAST     AS INT  END SELECT CASE WHEN ISNUMERIC  4 4     1 THEN CAST  4 4  AS INT  END SELECT CASE WHEN ISNUMERIC  1 300     1 THEN CAST  1 300  AS INT  END  -- Error  overflow   SELECT CASE WHEN ISNUMERIC  9999999999     1 THEN CAST  9999999999  AS INT  END   If you want a reliable conversion  you ll need to code one yourself    Update  My new recommendation would be to use an intermediary test conversion to FLOAT to validate the number  This approach is based on adrianm s comment  The logic can be defined as an inline table-valued function   CREATE FUNCTION TryConvertInt   text NVARCHAR MAX    RETURNS TABLE AS RETURN       SELECT         CASE WHEN ISNUMERIC  text     e0     1 THEN               CASE WHEN CONVERT FLOAT   text  BETWEEN -2147483648 AND 2147483647                    THEN CONVERT INT   text                END           END AS  Result      Some tests   SELECT  Conversion   Result  FROM   VALUES         1234                          -- 1234         1 234                         -- NULL         1234 0                        -- NULL         -1234                         -- -1234          1234                         -- NULL         1234e10                       -- NULL         1234 5678                     -- NULL         123-456                       -- NULL         1234 5                        -- NULL         123456789000000               -- NULL         N A                           -- NULL         -                             -- NULL                                       -- NULL         4 4                           -- NULL         1 300                         -- NULL         9999999999                    -- NULL         00000000000000001234          -- 1234         212110090000000235698741      -- NULL   AS  Source    Text   OUTER APPLY TryConvertInt   Source   Text   AS  Conversion    Results are similar to Joseph Sturtevant s answer  with the following main differences    My logic does not tolerate occurrences of   or   in order to mimic the behaviour of native INT conversions   1 234  and  1234 0  return NULL  Since it does not use local variables  my function can be defined as an inline table-valued function  allowing for better query optimization  Joseph s answer can lead to incorrect results due to silent truncations of the argument   00000000000000001234  evaluates to 12  Increasing the parameter length would result in errors on numbers that overflow BIGINT  such as BBANs  basic bank account numbers  like  212110090000000235698741     Withdrawn  The approach below is no longer recommended  as is left just for reference   The snippet below works on non-negative integers  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   However  it also gives NULL for valid integers whose length exceeds 10 characters due to leading zeros   SELECT      CASE WHEN  text NOT LIKE     0-9    THEN          CASE WHEN LEN  text  BETWEEN 1 AND 9                  OR LEN  text    10 AND  text  lt    2147483647                 THEN CAST   text AS INT           END     END    If you want to support any number of leading zeros  use the below  The nested CASE statements  albeit unwieldy  are required to promote short-circuit evaluation and reduce the likelihood of errors  arising  for example  from passing a negative length to LEFT    SELECT      CASE WHEN  text NOT LIKE     0-9    THEN          CASE WHEN LEN  text  BETWEEN 1 AND 9 THEN CAST   text AS INT                WHEN LEN  text   gt   10 THEN               CASE WHEN LEFT  text  LEN  text  - 10  NOT LIKE     0                        AND RIGHT  text  10   lt    2147483647                     THEN CAST   text AS INT                END          END     END   If you want to support positive and negative integers with any number of leading zeros   SELECT           -- Positive integers  or 0       CASE WHEN  text NOT LIKE     0-9    THEN          CASE WHEN LEN  text  BETWEEN 1 AND 9 THEN CAST   text AS INT                WHEN LEN  text   gt   10 THEN               CASE WHEN LEFT  text  LEN  text  - 10  NOT LIKE     0                        AND RIGHT  text  10   lt    2147483647                     THEN CAST   text AS INT                END          END          -- Negative integers           WHEN LEFT  text  1     -  THEN          CASE WHEN RIGHT  text  LEN  text  - 1  NOT LIKE     0-9    THEN               CASE WHEN LEN  text  BETWEEN 2 AND 10 THEN CAST   text AS INT                     WHEN LEN  text   gt   11 THEN                    CASE WHEN SUBSTRING  text  2  LEN  text  - 11  NOT LIKE     0                             AND RIGHT  text  10   lt    2147483648                          THEN CAST   text AS INT                     END               END          END     END

User · Answer

Regards   I wrote a useful scalar function to simulate the TRY CAST function of SQL SERVER 2012 in SQL Server 2008   You can see it in the next link below and we help each other to improve it  TRY CAST Function for SQL Server 2008 https   gist github com jotapardo 800881eba8c5072eb8d99ce6eb74c8bb     The two main differences are that you must pass 3 parameters and you   must additionally perform an explicit CONVERT or CAST to the field    However  it is still very useful because it allows you to return a   default value if CAST is not performed correctly    dbo TRY CAST Expression  Data Type  ReturnValueIfErrorCast    Example   SELECT   CASE WHEN dbo TRY CAST  6666666166666212    INT   DEFAULT  IS NULL                            THEN  Cast failed                            ELSE  Cast succeeded                        END AS Result     For now only supports the data types INT  DATE  NUMERIC  BIT and FLOAT  I hope you find it useful   CODE   DECLARE  strSQL NVARCHAR 1000  IF NOT EXISTS  SELECT   FROM dbo sysobjects WHERE id   OBJECT ID N  dbo   TRY CAST         BEGIN         SET  strSQL    CREATE FUNCTION  dbo   TRY CAST     RETURNS INT AS BEGIN RETURN 0 END          EXEC sys sp executesql  strSQL     END  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO     ------------------------------------------------------------------------------------------------------------------------     Description                          Syntax                      ---------------                     dbo TRY CAST Expression  Data Type  ReturnValueIfErrorCast                        --------------------------- -----------------------                          Expression                  VARCHAR 8000                               --------------------------- -----------------------                          Data Type                   VARCHAR 8000                               --------------------------- -----------------------                          ReturnValueIfErrorCast      SQL VARIANT   NULL                         --------------------------- -----------------------                        Arguments                     ---------------                     expression                     The value to be cast  Any valid expression                       Data Type                     The data type into which to cast expression                       ReturnValueIfErrorCast                     Value returned if cast fails or is not supported  Required  Set the DEFAULT value by default                        Return Type                     ----------------                     Returns value cast to SQL VARIANT type if the cast succeeds  otherwise  returns null if the parameter  pReturnValueIfErrorCast is set to DEFAULT                       or that the user indicates                        Remarks                     ----------------                     dbo TRY CAST function simulates the TRY CAST function reserved of SQL SERVER 2012 for using in SQL SERVER 2008                       dbo TRY CAST function takes the value passed to it and tries to convert it to the specified Data Type                       If the cast succeeds  dbo TRY CAST returns the value as SQL VARIANT type  if the cast doesn  t succees  null is returned if the parameter  pReturnValueIfErrorCast is set to DEFAULT                       If the Data Type is unsupported will return  pReturnValueIfErrorCast                      dbo TRY CAST function requires user make an explicit CAST or CONVERT in ANY statements                      This version of dbo TRY CAST only supports CAST for INT  DATE  NUMERIC and BIT types                        Examples                                                                                                                                               --A  Test TRY CAST function returns null                          SELECT                                CASE WHEN dbo TRY CAST  6666666166666212    INT   DEFAULT  IS NULL                                THEN  Cast failed                                ELSE  Cast succeeded                            END AS Result                        GO                      --B  Error Cast With User Value                          SELECT                                dbo TRY CAST  2147483648    INT   DEFAULT  AS  Error Cast With DEFAULT                               dbo TRY CAST  2147483648    INT   -1  AS  Error Cast With User Value                               dbo TRY CAST  2147483648    INT   NULL  AS  Error Cast With User NULL Value                             GO                       --C  Additional CAST or CONVERT required in any assignment statement                          DECLARE  IntegerVariable AS INT                          SET  IntegerVariable   CAST dbo TRY CAST 123   INT   DEFAULT  AS INT                           SELECT  IntegerVariable                          GO                           IF OBJECT ID  tempdb   temp   IS NOT NULL                             DROP TABLE  temp                          CREATE TABLE  temp                               Id INT IDENTITY                               FieldNumeric NUMERIC 3  1                                                         INSERT INTO dbo  temp  FieldNumeric                          SELECT CAST dbo TRY CAST 12 3   NUMERIC 3 1    0  AS NUMERIC 3  1   --Need explicit CAST on INSERT statements                          SELECT                           FROM  temp                          DROP TABLE  temp                          GO                       --D  Supports CAST for INT  DATE  NUMERIC and BIT types                           SELECT dbo TRY CAST 2147483648   INT   0  AS  Cast failed                                dbo TRY CAST 2147483647   INT   0  AS  Cast succeeded                                SQL VARIANT PROPERTY dbo TRY CAST 212   INT   0    BaseType   AS  BaseType                            SELECT dbo TRY CAST  AAAA0101    DATE   DEFAULT  AS  Cast failed                                dbo TRY CAST  20160101    DATE   DEFAULT  AS  Cast succeeded                                SQL VARIANT PROPERTY dbo TRY CAST  2016-01-01    DATE   DEFAULT    BaseType   AS  BaseType                            SELECT dbo TRY CAST 1 23   NUMERIC 3 1    DEFAULT  AS  Cast failed                                dbo TRY CAST 12 3   NUMERIC 3 1    DEFAULT  AS  Cast succeeded                                SQL VARIANT PROPERTY dbo TRY CAST 12 3   NUMERIC 3 1    DEFAULT    BaseType   AS  BaseType                            SELECT dbo TRY CAST  A    BIT   DEFAULT  AS  Cast failed                                dbo TRY CAST 1   BIT   DEFAULT  AS  Cast succeeded                                SQL VARIANT PROPERTY dbo TRY CAST  123    BIT   DEFAULT    BaseType   AS  BaseType                            GO                       --E  B  TRY CAST return NULL on unsupported data types                          SELECT dbo TRY CAST 4   xml   DEFAULT  AS  unsupported                              GO                                                                                                                              ------------------------------------------------------------------------------------------------------------------------     Responsible     Javier Pardo      Date            diciembre 29 2016     WB tests        Javier Pardo  ------------------------------------------------------------------------------------------------------------------------     Update by       Javier Eduardo Pardo Moreno      Date            febrero 16 2017     Id update       JEPM20170216     Description     Fix  ISNUMERIC function makes it unreliable  SELECT dbo TRY CAST       INT   0  will yield Msg 8114                       Level 16  State 5  Line 16 Error converting data type varchar to float                      ISNUMERIC   function treats few more characters as numeric  like       minus      plus      dollar      back slash      dot and    comma                     Collaborator aperiooculus  http   stackoverflow com users 3083382 aperiooculus                        Fix dbo TRY CAST  2013 09 20    datetime   DEFAULT  for supporting DATETIME format      WB tests        Javier Pardo   ------------------------------------------------------------------------------------------------------------------------     ALTER FUNCTION dbo TRY CAST        pExpression AS VARCHAR 8000        pData Type AS VARCHAR 8000        pReturnValueIfErrorCast AS SQL VARIANT   NULL   RETURNS SQL VARIANT AS BEGIN     --------------------------------------------------------------------------------     --  INT      --------------------------------------------------------------------------------      IF  pData Type    INT      BEGIN         IF ISNUMERIC  pExpression    1 AND  pExpression NOT IN   -                          --JEPM20170216         BEGIN             DECLARE  pExpressionINT AS FLOAT   CAST  pExpression AS FLOAT               IF  pExpressionINT BETWEEN - 2147483648 0 AND 2147483647 0             BEGIN                 RETURN CAST  pExpressionINT as INT              END             ELSE             BEGIN                 RETURN  pReturnValueIfErrorCast             END --FIN IF  pExpressionINT BETWEEN - 2147483648 0 AND 2147483647 0         END         ELSE         BEGIN             RETURN  pReturnValueIfErrorCast         END -- FIN IF ISNUMERIC  pExpression    1     END -- FIN IF  pData Type    INT       --------------------------------------------------------------------------------     --  DATE         --------------------------------------------------------------------------------      IF  pData Type IN   DATE   DATETIME       BEGIN         IF ISDATE  pExpression    1         BEGIN              DECLARE  pExpressionDATE AS DATETIME   cast  pExpression AS DATETIME               IF  pData Type    DATE              BEGIN                 RETURN cast  pExpressionDATE as DATE              END              IF  pData Type    DATETIME              BEGIN                 RETURN cast  pExpressionDATE as DATETIME              END          END         ELSE          BEGIN              DECLARE  pExpressionDATEReplaced AS VARCHAR 50    REPLACE REPLACE REPLACE  pExpression                  -                   IF ISDATE  pExpressionDATEReplaced    1             BEGIN                 IF  pData Type    DATE                  BEGIN                     RETURN cast  pExpressionDATEReplaced as DATE                  END                  IF  pData Type    DATETIME                  BEGIN                     RETURN cast  pExpressionDATEReplaced as DATETIME                  END              END             ELSE             BEGIN                 RETURN  pReturnValueIfErrorCast             END         END --FIN IF ISDATE  pExpression    1     END --FIN IF  pData Type    DATE       --------------------------------------------------------------------------------     --  NUMERIC      --------------------------------------------------------------------------------      IF  pData Type LIKE  NUMERIC       BEGIN          IF ISNUMERIC  pExpression    1         BEGIN              DECLARE  TotalDigitsOfType AS INT   SUBSTRING  pData Type CHARINDEX      pData Type  1   CHARINDEX      pData Type  - CHARINDEX      pData Type  - 1                     TotalDecimalsOfType AS INT   SUBSTRING  pData Type CHARINDEX      pData Type  1   CHARINDEX      pData Type  - CHARINDEX      pData Type  - 1                     TotalDigitsOfValue AS INT                     TotalDecimalsOfValue AS INT                     TotalWholeDigitsOfType AS INT                     TotalWholeDigitsOfValue AS INT               SET  pExpression   REPLACE  pExpression                        SET  TotalDigitsOfValue   LEN REPLACE  pExpression                       SET  TotalDecimalsOfValue   CASE Charindex       pExpression                                          WHEN 0                                             THEN 0                                         ELSE Len Cast Cast Reverse CONVERT VARCHAR 50    pExpression  128   AS FLOAT  AS BIGINT                                           END              SET  TotalWholeDigitsOfType    TotalDigitsOfType -  TotalDecimalsOfType             SET  TotalWholeDigitsOfValue    TotalDigitsOfValue -  TotalDecimalsOfValue              -- The total digits can not be greater than the p part of NUMERIC  p  s              -- The total of decimals can not be greater than the part s of NUMERIC  p  s              -- The total digits of the whole part can not be greater than the subtraction between p and s             IF   TotalDigitsOfValue  lt    TotalDigitsOfType  AND   TotalDecimalsOfValue  lt    TotalDecimalsOfType  AND   TotalWholeDigitsOfValue  lt    TotalWholeDigitsOfType              BEGIN                 DECLARE  pExpressionNUMERIC AS FLOAT                 SET  pExpressionNUMERIC   CAST  ROUND  pExpression   TotalDecimalsOfValue  AS FLOAT                    RETURN  pExpressionNUMERIC --Returns type FLOAT             END              else             BEGIN                 RETURN  pReturnValueIfErrorCast             END-- FIN IF   TotalDigitisOfValue  lt    TotalDigits  AND   TotalDecimalsOfValue  lt    TotalDecimals            END         ELSE          BEGIN             RETURN  pReturnValueIfErrorCast         END --FIN IF ISNUMERIC  pExpression    1     END --IF  pData Type LIKE  NUMERIC        --------------------------------------------------------------------------------     --  BIT      --------------------------------------------------------------------------------      IF  pData Type LIKE  BIT      BEGIN         IF ISNUMERIC  pExpression    1         BEGIN             RETURN CAST  pExpression AS BIT           END         ELSE          BEGIN             RETURN  pReturnValueIfErrorCast         END --FIN IF ISNUMERIC  pExpression    1     END --IF  pData Type LIKE  BIT        --------------------------------------------------------------------------------     --  FLOAT        --------------------------------------------------------------------------------      IF  pData Type LIKE  FLOAT      BEGIN         IF ISNUMERIC REPLACE REPLACE  pExpression  CHAR 13        CHAR 10          1         BEGIN              RETURN CAST  pExpression AS FLOAT           END         ELSE          BEGIN              IF REPLACE  pExpression  CHAR 13            --Only white spaces are replaced  not new lines             BEGIN                 RETURN 0             END             ELSE              BEGIN                 RETURN  pReturnValueIfErrorCast             END --IF REPLACE  pExpression  CHAR 13                      END --FIN IF ISNUMERIC  pExpression    1     END --IF  pData Type LIKE  FLOAT       --------------------------------------------------------------------------------     --  Any other unsupported data type will return NULL or the value assigned by the user to  pReturnValueIfErrorCast       --------------------------------------------------------------------------------      RETURN  pReturnValueIfErrorCast    END

User · Answer

Yes     Try this   DECLARE  text AS NVARCHAR 10   SET  text    100  SELECT CASE WHEN ISNUMERIC  text    1 THEN CAST  text AS INT  ELSE NULL END -- returns 100  SET  text    XXX  SELECT CASE WHEN ISNUMERIC  text    1 THEN CAST  text AS INT  ELSE NULL END -- returns NULL   ISNUMERIC   has a few issues pointed by Fedor Hajdu    It returns true for strings like    is currency     or    both are separators     and -

User · Answer

Joseph s answer pointed out ISNUMERIC also handles scientific notation like  1 3e 3  but his answer doesn t handle this format of number   Casting to a money or float first handles both the currency and scientific issues   IF  EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   TryConvertInt    AND type in  N FN   N IF   N TF   N FS   N FT    DROP FUNCTION  dbo   TryConvertInt  GO  CREATE FUNCTION dbo TryConvertInt  Value varchar 18   RETURNS bigint AS BEGIN     DECLARE  IntValue bigint       IF  ISNUMERIC  Value    1          IF   Value like   e                SET  IntValue   CAST Cast  Value as float  as bigint           ELSE             SET  IntValue   CAST CAST  Value as money  as bigint       ELSE         SET  IntValue   NULL       RETURN  IntValue  END   The function will fail if the number is bigger than a bigint   If you want to return a different default value  leave this function so it is generic and replace the null afterwards   SELECT IsNull dbo TryConvertInt  nan     1000

User · Answer

If you are on SQL Server 2012  or newer   Use the TRY CONVERT function  If you are on SQL Server 2005  2008  or 2008 R2  Create a user defined function  This will avoid the issues that Fedor Hajdu mentioned with regards to currency  fractional numbers  etc  CREATE FUNCTION dbo TryConvertInt  Value varchar 18   RETURNS int AS BEGIN     SET  Value   REPLACE  Value               IF ISNUMERIC  Value    e0     0 RETURN NULL     IF   CHARINDEX       Value   gt  0 AND CONVERT bigint  PARSENAME  Value  1    lt  gt  0   RETURN NULL     DECLARE  I bigint           CASE         WHEN CHARINDEX       Value   gt  0 THEN CONVERT bigint  PARSENAME  Value  2           ELSE CONVERT bigint   Value          END     IF ABS  I   gt  2147483647 RETURN NULL     RETURN  I END GO  -- Testing DECLARE  Test TABLE Value nvarchar 50      -- Result INSERT INTO  Test SELECT  1234             -- 1234 INSERT INTO  Test SELECT  1 234            -- 1234 INSERT INTO  Test SELECT  1234 0           -- 1234 INSERT INTO  Test SELECT  -1234            -- -1234 INSERT INTO  Test SELECT   1234            -- NULL INSERT INTO  Test SELECT  1234e10          -- NULL INSERT INTO  Test SELECT  1234 5678        -- NULL INSERT INTO  Test SELECT  123-456          -- NULL INSERT INTO  Test SELECT  1234 5           -- NULL INSERT INTO  Test SELECT  123456789000000  -- NULL INSERT INTO  Test SELECT  N A              -- NULL SELECT Value  dbo TryConvertInt Value  FROM  Test  Reference  I used this page extensively when creating my solution

User · Answer

I know it s not pretty but it is simple  Try this   declare  AlpaNumber nvarchar 50     ABC  declare  MyNumber int   0 begin Try select  MyNumber   case when ISNUMERIC  AlpaNumber    1 then cast  AlpaNumber as int  else 0 end End Try Begin Catch     -- Do nothing End Catch   if exists select   from mytable where mynumber    MyNumber  Begin print  Found  End Else Begin  print  Not Found  End

User · Answer

I would rather create a function like TryParse or use T-SQL TRY-CATCH block to get what you wanted   ISNUMERIC doesn t always work as intended  The code given before will fail if you do   SET  text          sign can be converted to money datatype  so ISNUMERIC   returns true in that case  It will do the same for  -   minus        comma  and     characters

[sql] TSQL - Cast string to integer or return default value

Examples related to sql

Examples related to tsql