Need Help with Conversion

Databases
1

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?

CAST(CAST(p.ProductID as varchar(20)) as INT)
2

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

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

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

5

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.