SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help with Conversion

    I am doing a data migration from source SQL DB to target SQL DB. The data type in a particular source column is text and the target column is data type int in the product table. The problem is the user entered in product numbers with letters in the source table. I need to insert the rows with that product number to the new table that has a data type of int. SQL doesn't like it. I tried cast the value to int and get an error. Is it possible to cast a text data type value like 333C to int?

    Code SQL:
    CAST(CAST(p.ProductID AS VARCHAR(20)) AS INT)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    And what should it become? Where does that 'C' go?
    Of course, you'll have to convert each product id the same way in all tables, otherwise you'll loose the relationships. And of course, each new product id must be unique.

    Maybe you could convert each letter in a number? Like:

    A -> 01
    B -> 02
    C -> 03
    etc,

    so your 333C would become 33303.

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem I have is that the product id must remain the same from source table to target. I don't think that it is possible.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by rlowe711 View Post
    The problem I have is that the product id must remain the same from source table to target. I don't think that it is possible.
    you're right, it isn't possible, not with INTEGER as the target datatype

    p.s. almost every database system is an "SQL" database system

    avoid confusion, call it SQL Server if that's what you're running
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Maybe this is just because of the example chosen, but is this actually a text field, or a text field being used to hold Hex numbers? If it's hex, just run a Hex-To-Dec conversion on it.

    If not, you're going to need to be very careful about uniqueness.


Bookmarks

Posting Permissions

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