Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL - Cast string to integer or return default value

Tags:

sql

tsql

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

like image 396
Oliver Hanappi Avatar asked Jan 04 '10 15:01

Oliver Hanappi


People also ask

Is it better to use CAST () or convert ()?

CAST is also less powerful and less flexible than CONVERT. On the other hand, CONVERT allows more flexibility and is the preferred function to use for data, time values, traditional numbers, and money signifiers. CONVERT is also useful in formatting the data's format. What is this?

Can you CAST a string to an int in SQL?

CAST and CONVERT can be used to convert a string to a number of any data type. For example, you can convert a string to a number of data type INTEGER. TO_DATE converts a formatted date string to a date integer. TO_TIMESTAMP converts a formatted date and time string to a standard timestamp.

What is difference between CAST and convert in SQL?

CAST is part of the ANSI-SQL specification; whereas, CONVERT is not. In fact, CONVERT is SQL implementation-specific. CONVERT differences lie in that it accepts an optional style parameter that is used for formatting.

What is CAST () and convert () functions in SQL Server?

The cast and convert functions provide similar functionality. They are used to convert a value from one data type to another. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.


8 Answers

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

like image 76
Grzegorz Gierlik Avatar answered Sep 29 '22 09:09

Grzegorz Gierlik


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) > 0 AND CONVERT(bigint, PARSENAME(@Value, 1)) <> 0 ) RETURN NULL
    DECLARE @I bigint =
        CASE
        WHEN CHARINDEX('.', @Value) > 0 THEN CONVERT(bigint, PARSENAME(@Value, 2))
        ELSE CONVERT(bigint, @Value)
        END
    IF ABS(@I) > 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.

like image 37
Joseph Sturtevant Avatar answered Sep 29 '22 07:09

Joseph Sturtevant


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.

like image 37
Fedor Hajdu Avatar answered Sep 29 '22 09:09

Fedor Hajdu


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 <= '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) >= 10 THEN
              CASE WHEN LEFT(@text, LEN(@text) - 10) NOT LIKE '%[^0]%'
                    AND RIGHT(@text, 10) <= '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) >= 10 THEN
              CASE WHEN LEFT(@text, LEN(@text) - 10) NOT LIKE '%[^0]%'
                    AND RIGHT(@text, 10) <= '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) >= 11 THEN
                   CASE WHEN SUBSTRING(@text, 2, LEN(@text) - 11) NOT LIKE '%[^0]%'
                         AND RIGHT(@text, 10) <= '2147483648'
                        THEN CAST (@text AS INT)
                   END
              END
         END
    END
like image 22
Douglas Avatar answered Sep 29 '22 09:09

Douglas


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 <= @TotalDigitsOfType) AND (@TotalDecimalsOfValue <= @TotalDecimalsOfType) AND (@TotalWholeDigitsOfValue <= @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 <= @TotalDigits) AND (@TotalDecimalsOfValue <= @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
like image 35
JotaPardo Avatar answered Sep 29 '22 07:09

JotaPardo


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);
like image 44
Stephen Turner Avatar answered Sep 29 '22 07:09

Stephen Turner


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
like image 44
Ramblen Man Avatar answered Sep 29 '22 09:09

Ramblen Man


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)<>1 OR
      (LEFT(@Value,1)='-' AND LEN(@Value)>11) OR
      (LEFT(@Value,1)='-' AND LEN(@Value)=11 AND @Value>@MinValue) OR
      (LEFT(@Value,1)<>'-' AND LEN(@Value)>10) OR
      (LEFT(@Value,1)<>'-' AND LEN(@Value)=10 AND @Value>@MaxValue)
      GOTO FINISHED

    DECLARE @cnt int = 0
    WHILE @cnt<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
like image 25
Brian Payne Avatar answered Sep 29 '22 07:09

Brian Payne