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 |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2009-12-15 : 23:20:08
|
| Greetings all,I have a few name called TransAmount. I migrated the table containing this fieldname and host of others.This TransAmount was defined as nvarchar(255) but I want to convert it to Money data type.So far, there are over 300 records.Some of the values are entered as follows:34.00, -45.00, 75.00-, 60Notice that in the above examples, I have periods (.), and dashes (-).I have been manually removing the dashes and periods but I am sure there is a better, faster, more efficient way of messaging this till I can change the data type to Money.Is there a script or a way to do this all at once?For instance, is there a script that can remove any value that has a period, say 250.00 and convert that value to just 250Also, is there a way to remove dashes from a value?I hope I am clear in what I am asking for.Your assistance is greatly appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2009-12-16 : 00:01:16
|
| hi Tara,I do have the dashes on both sides of the value though.some show 139- and some show -23Currently, I am using select cast(fieldname as money) from... I am just trying to clean up the values.Thanks a lot for your prompt response. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-16 : 00:05:43
|
| What I'm saying is that you'll need to clean up the bad data first. Anything that can't be converted via CONVERT needs to be cleaned up first. 139- is an example that needs to be cleaned up first. Search for those use the RIGHT function and shift that negative sign to the beginning. Then run your CONVERT(money, ...).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-12-16 : 01:57:03
|
| To identify the bad data, useselect col from your_tablewhere col like '%[0-9]-%'If you findselect cast(col as money) from your_tablewhere col not like '%[0-9]-%'MadhivananFailing to plan is Planning to fail |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2009-12-16 : 08:59:01
|
| We are on the same page here Tara as far as cleaning data is concerned.My issue is that I would prefer not to do them manually because there are several of them.I was wondering if there is a script out there that can help me clean them in one fell swoop.Thanks for your help.madhivanan: Thanks for your help as well.I can certainly use the first part of your code to identify data that needs scrubbing.I just need to find the script that will change them in the db, rather then casting or converting which I can still use as extra protection.Thanks to both of you for your assistance. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-12-16 : 09:08:32
|
| TryBegin TransactionUpdate your_tableset col=substring(col,1,len(col)-1)where col like '%[0-9]-%'--See if this gives the data you wantselect col from your_table--If so, commit itCommit Transaction--If not, rollback itRollback transactionMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
varchar to money data type???