Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006

    Question Unanswered: Data Type Conversion Error ----- TRICKY QUESTION !!

    I have a table A having a varchar(30) column.
    The data from this table is being inserted into another table B having a int column.

    create table A
    ( a varchar(30))

    create table B
    (b int)

    insert into A(a) values("1")
    insert into A(a) values("2Z")
    insert into A(a) values("T7713080")
    insert into A(a) values("100")

    Below statement fails when there are nonnumeric charcaters in table A.
    insert into B select convert(int,a) from A

    I want to find the offending records which are failing the above insert (2nd,3rd records) in the above case,but continue
    with the processing of the remaining records.Any idea on how to do it?

    @@error check does not work as the statement fails for "syntax error while converting varchar to int"
    and doesn't get executed when it reaches the second record.

  2. #2
    Join Date
    May 2005

    I am not sure you can that directly,
    maybe could you try to convert in numeric on a first step ?

    what is your ASE version ?

    an old bug exist :

    Title str() and convert from int to char obtain wrong results on 11.9.3 HP

    Converting from integer to varchar results in wrong numbers for negative values. The bugfix of CR 204499 looks like 'done'. But the bugfix only fixed the converting from integer to CHAR. The problem is still existing for converting integer to VARCHAR

  3. #3
    Join Date
    Jun 2006


    Even a indirect way would do.. A sp for this functionality.

    Forgot to mention earlier...My ASE version is 12.5

    TSQL suggests that there is a function isnumeric() but I am getting errors in using it.Does ASE 12.5 support this?

    Could you please elaborate on how numeric conversion willhelp in resolving this?

    The requirement is........
    1)to find out all nonnumeric records from first table(A) and copy them to some other table(C) and continue with processing for table simple terma just find all non numeric records from a varchar column.

    Wonder...what a isinteger() built in would have done?

  4. #4
    Join Date
    May 2005
    South Africa
    Provided Answers: 1
    insert into B select convert(int,a) from A
    where patindex('%[^0-9]%',a)=0

Posting Permissions

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