Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
skybvi
Posting Yak Master
193 Posts |
 Posted - 2011-08-26 : 10:34:45
|
| Hi, I have 2 tables to join on a common field(column) having char and PLU_NUM(float) values..They have values like ..298748372690 (all numbers)I cant edit the table with PLU_NUM(float) but can change the other table having values in char.So i need to change all char values to PLU_NUM(float)..How is it possible.Thanks.Regards,SushantDBAWest Indies |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-08-26 : 10:54:06
|
| Joining on Floating point numbers is inherently dangerous because they are not precise so I'm a bit wary to offer advice.Can you give us the table definitions and some sample data.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-26 : 10:54:40
|
| how can you convert the nonnumeric data to float?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2011-08-26 : 11:06:06
|
| @transact charlie..One table has UPC(char)(common column) values as :-780295006947 750105920637 1500000701 1500000802 1500000702 Other table has ITM_ID(PLU_NUM(float))(common column) values as :-11509004111150900422115090046211509007081150900713I want to join both tables on common values..BUT getting error cannot convert varchar to float.Regards,SushantDBAWest Indies |
|
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2011-08-26 : 12:30:02
|
| Hi, I changed the data types successfully..I used SELECT CAST(mycolname AS float) FROM tablenameSELECT CONVERT(FLOAT, mycolname) FROM tablenameAfter running above statments,I still got same error "it cannot convert varchar to float" but that it stopped after showing some resultsso i saw that the next column values wich it cannot do were like '344723-547'so i removed '-' from the valuesand then again i ran CAST and CONVERT and then when i went into table name right click design, i would change it to float. :)ThanksRegards,SushantDBAWest Indies |
|
|
|
|
|
|
char to PLU_NUM(float)