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 |
|
Chiweez
Starting Member
4 Posts |
 Posted - 2011-03-31 : 19:16:01
|
| Hi all,**Newbie alert**I have a column (ICD) inside a table that is already set up for decimal type.The format of the values that I'm expecting is for the decimal to be 2 places to the right of the first character (so 1234 is 12.34 & 123 is 12.3)In looking through the table recently I spotted 6,000 plus rows with no decimal point. I need an update query that will put the decimal point where I need it.Any help is appreciated.Please let me know if I need to offer up more information. Thank you! |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-31 : 20:02:29
|
| That means that ICD is not a data type of decimal. What is it?JimEveryday I learn something that somebody else already knew |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-31 : 20:02:30
|
| That means that ICD is not a data type of decimal. What is it?JimEveryday I learn something that somebody else already knew |
|
|
|
Chiweez
Starting Member
4 Posts |
Posted - 2011-03-31 : 20:44:35
|
| Hi Jim,It seems I was mistaken. The data type is varchar. |
|
|
|
Chiweez
Starting Member
4 Posts |
Posted - 2011-04-01 : 15:11:41
|
| Is there nothing I can do in this case? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-01 : 15:50:23
|
| Kinda scary, but this works. Test it with select statements first to make it sure it does exactly what you want!declare @string varchar(20)set @string = '123456'select left(@string,2)+'.'+substring(@string,3,20)only run it on records without the '.'UPDATE yourTableSET ICD = leftICD,2)+'.'+substring(ICD,3,20)WHERE ICD not like '%.%'Then altert the column to be a decimal typeJimEveryday I learn something that somebody else already knew |
|
|
|
Chiweez
Starting Member
4 Posts |
Posted - 2011-04-01 : 16:18:37
|
You sir, are a freakin rock star!!!Thank you so very much! You're gonna make me look like the genius at work.  |
|
|
|
|
|
|
UPDATE query help