Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2010
    Posts
    1

    Unanswered: Data conversion error

    Hi,

    I have been trying to convert nvchar into int but I get this error message
    "Conversion failed when converting the nvarchar value '
    691' to data type int".
    There is a meta character before the number 6. It shows as a small box when copied onto text. How do i eliminate this entry?
    I want to convert all entries other than those which contains the small box character.

    Can you please help me with this problem?

    Below is the snapshot of this problem:

    AAAAAAAAA BBBB CCCC
    670003945 0 99 8033
    670000086 3780 8033
    670000088 2940 8033

    In column BBBB, the first entry is not being converted to int. The error "Conversion failed when converting the nvarchar value '0 99' to data type int" is thrown.


    Thanks.
    Last edited by nikhil_s; 09-07-10 at 18:13.

  2. #2
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    can you not do a replace on the value before converting to an int.

    select convert(int,(replace, bbbb, '?', ''))

    the question mark being your funky character not a question mark.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try to look at the contents of that column in that record with a hex editor.

    Otherwise you can find out by trial and error what hidden character is present in your table, by using :
    Code:
    SELECT *
    FROM MyTable
    Where CHARINDEX (char(36), ColumnName) > 0
    In this case char(36) stand for the character '$'. But this may take a long time.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I use a numbers table for that sort of thing. Variations on the below can make isolating the problem characters easier:
    Code:
    USE test
    GO
    
    DECLARE    @problem_chars  TABLE
        (
            the_text        VARCHAR(100)
        )
    
    INSERT INTO @problem_chars
        (
            the_text
        )
    VALUES  ('Find the ASCII values')
          , ('12345' + CHAR(12) + '5789')
          
    SELECT  problem_chars.the_text
          , the_char                = SUBSTRING(problem_chars.the_text, number, 1)
          , the_ASCII               = ASCII(SUBSTRING(problem_chars.the_text, number, 1))
    FROM    @problem_chars  AS problem_chars
    INNER JOIN
            dbo.numbers
    ON  numbers.number      BETWEEN 1 AND LEN(problem_chars.the_text)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Nice.




    But nice alone is too short to post.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    If, as you said, you only want to convert entries that do not contain the extraneous characters, then simply, in the WHERE clause, add:

    isnumeric(BBBB)=1

    This will test the BBBB field before the conversion attempt and exclude that record.

    If, however, you want to convert the entry, but simply exclude that single character, then you are going to have to flush-out that character.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dunno if you've seen this with ISNUMERIC - it does return the correct result, but that does not mean the input can be converted to INT, or even necessarily a numeric datatype. In particular, CHAR(12) is considered numeric.

    Code:
    SELECT  numchar
          , isnumeric       = ISNUMERIC(numchar)
          , numbertype      = CASE 
                                  WHEN numchar LIKE '%[^0-9.]%' THEN
                                      'Not Numeric'
                                  WHEN numchar LIKE '%[.]%' THEN
                                      'FLOAT'
                                  ELSE
                                      'INT'
                              END
    FROM    (
                VALUES  ('1D10')
                      , ('10e4')
                      , ('$')
                      , ('10e4')
                      , ('')
                      , (CHAR(9))
                      , (CHAR(12))
                      , ('123')
                      , ('123.456')
                      , ('ABC')
            ) AS numchars(numchar)
    I would use LIKE and wildcards.
    Last edited by pootle flump; 09-15-10 at 06:29.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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