Results 1 to 12 of 12

Thread: Convert Problem

  1. #1
    Join Date
    Aug 2007
    Posts
    7

    Unanswered: Convert Problem

    Hello,

    I have a stored procedure in SQL Server 2000 and I am trying to do something like this for example:
    declare @s varchar(50), @i smallint

    if isnumeric(@s) = 1
    begin
    select @i = convert(smallint, @s)
    if @@error <> 0
    print 'error'
    end
    else
    print 'No error'

    The problem is what can I do if the value of @s is '12.35'? This generates the 'Syntax error converting the varchar value to a column of data type smallint.' because the numeric value is actually decimal and the error is not caught by the @@error check.
    The only alternative I can think of is to convert @s to decimal the convert again but this is not suitable as this is for validation purposes and if the value is decimal I need to identify it and ignore it.

    Is there any way to check for or handle this scenario?

    Thanks for any help.
    AC

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are you checking to see if a VARCHAR(50) field contains a number?
    But hey, I'm sure you know what you're doing...
    Code:
    if isnumeric(@s) = 1
    begin
    select @i = convert(smallint, @s)
    end
    if @@error <> 0
    print 'error'
    end
    else begin
    print 'No error'
    end
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    declare @s varchar(50), @i smallint

    set @s = '12.35'

    if isnumeric(@s) = 1
    begin
    select @i = floor(@s) -- round(@s, 10, 0)
    if @@error <> 0
    print 'error'
    end
    else
    print 'No error'

    select @i

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Please note that the differences in the statement set up.

    I have separated the Isnumeric into it's own If.
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2007
    Posts
    7
    Thanks for the input Peso.
    However, this is not what I want to do. For validation purposes I want to catch this value if it is invalid and ignore it, not attempt to convert it to a valid value.

  6. #6
    Join Date
    Aug 2007
    Posts
    7
    Thanks georgev but this doesn't make a difference.
    The error returns before the process gets to checking @@error.


    Quote Originally Posted by georgev
    Please note that the differences in the statement set up.

    I have separated the Isnumeric into it's own If.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest using:
    Code:
    DROP FUNCTION dbo.IsInt
    GO
    --  ptp  20070913  Test to see if a string could be an MS-SQL integer
    
    CREATE FUNCTION dbo.IsInt(
       @pcArg		NVARCHAR(50)
       )
    RETURNS INT
    AS BEGIN
       DECLARE
          @result		INT
    ,     @work		BIGINT
    
       SET @pcArg = Rtrim(LTrim(@pcArg))
       IF @pcArg LIKE '%[^-+0-9]%' SET @result = 0		-- Invalid character(s)
       ELSE IF 22 < DataLength(@pcArg) SET @result = 0	-- Impossibly long
       ELSE IF @pcArg LIKE '[0-9]' SET @result = 1		-- Singleton digit
       ELSE IF @pcArg LIKE '[-+]%[-+]%' SET @result = 0	-- Multiple signs
       ELSE
          BEGIN						-- Clean input
             SET @work = Convert(BIGINT, @pcArg)
             IF @work BETWEEN -2147483648 AND 2147483647
                SET @result = 1				-- Passed range check
             ELSE
                SET @result = 0				-- Out of range
          END
    
       RETURN @result
    END
    GO
    
    SELECT c, dbo.IsInt(c)
       FROM (SELECT '99999999999999999999' AS c
          UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
          UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
          UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
          UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
          UNION ALL SELECT '2147483647'  UNION ALL SELECT '2147483648'
          UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
          ) AS z
    This probably don't perform fantastically, but it ought to get the job done and I don't know of any input that will break it.

    -PatP

  8. #8
    Join Date
    Aug 2007
    Posts
    7
    Thanks Pat, I should be able to use some pattern matching.


    Quote Originally Posted by Pat Phelan
    I'd suggest using:
    Code:
    DROP FUNCTION dbo.IsInt
    GO
    --  ptp  20070913  Test to see if a string could be an MS-SQL integer
    
    CREATE FUNCTION dbo.IsInt(
       @pcArg		NVARCHAR(50)
       )
    RETURNS INT
    AS BEGIN
       DECLARE
          @result		INT
    ,     @work		BIGINT
    
       SET @pcArg = Rtrim(LTrim(@pcArg))
       IF @pcArg LIKE '%[^-+0-9]%' SET @result = 0		-- Invalid character(s)
       ELSE IF 22 < DataLength(@pcArg) SET @result = 0	-- Impossibly long
       ELSE IF @pcArg LIKE '[0-9]' SET @result = 1		-- Singleton digit
       ELSE IF @pcArg LIKE '[-+]%[-+]%' SET @result = 0	-- Multiple signs
       ELSE
          BEGIN						-- Clean input
             SET @work = Convert(BIGINT, @pcArg)
             IF @work BETWEEN -2147483648 AND 2147483647
                SET @result = 1				-- Passed range check
             ELSE
                SET @result = 0				-- Out of range
          END
    
       RETURN @result
    END
    GO
    
    SELECT c, dbo.IsInt(c)
       FROM (SELECT '99999999999999999999' AS c
          UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
          UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
          UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
          UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
          UNION ALL SELECT '2147483647'  UNION ALL SELECT '2147483648'
          UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
          ) AS z
    This probably don't perform fantastically, but it ought to get the job done and I don't know of any input that will break it.

    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I was surprised by your response, then realized that I'd written the function for INT and you wanted SMALLINT. You only need to change one line to:
    Code:
    IF @work BETWEEN -32768 AND 32767
    and you're "good to go" for SMALLINT testing.

    Sorry about that oversight!

    -PatP

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that I've found a better answer. This function checks a string argument to see if it can be an MS-SQL integer value, and it returns a string with a character for each data type that the argument could be. This won't win any speed prizes, but it avoids at least two of the pitfalls that I found in the prior function and it provides more functionality too.
    Code:
    DROP FUNCTION dbo.IntegerTypes
    GO
    --  ptp  20070913  Return which types of MS-SQL integer the argument could be
    
    CREATE FUNCTION dbo.IntegerTypes(
       @pcArg		NVARCHAR(39)
       )
    RETURNS VARCHAR(8)
    AS BEGIN
       DECLARE
          @result		VARCHAR(8)
    ,     @work		NUMERIC(38)
    
       SET @pcArg = Rtrim(LTrim(@pcArg))
       IF @pcArg LIKE '%[^-+0-9]%' SET @result = ''		-- Invalid character(s)
       ELSE IF @pcArg NOT LIKE '%[0-9]%' SET @result = ''	-- No digits
       ELSE IF @pcArg LIKE '[-+0-9]%[-+]%' SET @result = ''	-- Sign after sign or digit
       ELSE IF 78 < DataLength(@pcArg) SET @result = ''	-- Impossibly long
       ELSE IF 78 = DataLength(@pcArg) AND @pcArg NOT LIKE '[-+]%' SET @result = ''
       ELSE
          BEGIN						-- Clean input
             SET @result = 'N'
             SET @work = @pcArg
             IF @work BETWEEN -9223372036854775808 AND 9223372036854775807 SET @result = @result + 'B'	-- BIGINT
             IF @work BETWEEN          -2147483648 AND          2147483647 SET @result = @result + 'I'	-- INT
             IF @work BETWEEN               -32768 AND               32767 SET @result = @result + 'S'	-- SMALLINT
             IF @work BETWEEN                    0 AND                 255 SET @result = @result + 'T'	-- TINYINT
          END
    
       RETURN @result
    END
    GO
    
    SELECT c, dbo.IntegerTypes(c)
       FROM (SELECT '9999999999999999999999999999999999999999' AS c
          UNION ALL SELECT '99999999999999999999999999999999999999'
          UNION ALL SELECT '+99999999999999999999999999999999999999'
          UNION ALL SELECT '-99999999999999999999999999999999999999'
          UNION ALL SELECT '-2147483648' UNION ALL SELECT '-2147483649'
          UNION ALL SELECT '+2147483647' UNION ALL SELECT '+2147483648'
          UNION ALL SELECT ' 2147483647' UNION ALL SELECT ' 2147483648'
          UNION ALL SELECT '21474 83647' UNION ALL SELECT '-21474-83648'
          UNION ALL SELECT '2147483647'  UNION ALL SELECT '2147483648'
          UNION ALL SELECT '1' UNION ALL SELECT '-' UNION ALL SELECT '1.3'
          ) AS z
    -PatP

  11. #11
    Join Date
    Apr 2007
    Posts
    183
    WHERE Col1 NOT LIKE '%[^0-9]%'

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Peso
    WHERE Col1 NOT LIKE '%[^0-9]%'
    Code:
    SELECT Replicate('9', 99)
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •