Char to Decimal representation

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Char to Decimal representation

Author

Topic

jasscat
Starting Member

9 Posts

Posted - 2009-12-24 : 08:54:10

I have some columns with numbers in many, but not all, of the rows. The numbers are at most six digits in length. The numbers are stored in a CHAR data type. So a typical number would be something like 004728.

I know that this number represents a decimal number with two decimal places. I want to show it as a decimal, like 0047.28. If I convert the CHAR to DECIMAL, it ends up being 004728.00.

I've tried inserting the decimal place in the number like so:

declare @length int,
@part1 int,
@part2 int;
select @length = LEN(MyNumber) from MyTable;
select @part1 = (@length - 2);
select @part2 = (@part1 + 1);
select substring(MyNumber,1,@part1) + '.' + substring(MyNumber,@part2,2)
from MyTable;

I'm not happy with this solution because (1) it gags if there is no number present, (2) I'd like to do is as I'm inserting the number into a table variable column, and (3) it's cumbersome. I thought I could do something like:

@CharVariable = Select Substring(MyNumber,1,(LEN(MyNumber) - 2) + '.' + subtring(MyNumber,((LEN(MyNumber) - 2) + 1),2)

But I couldn't get this to work.

Any ideas?

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-24 : 09:07:48

Convert to Float Divide by 100 and then convert back to char if it must be in there as one.

You can also use something like ISNUMERIC() to check and see if you have a number and then doing all the conversion if so, if not leave it alone.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-24 : 09:21:09

select cast(MyNumber as decimal(12,2))/100 from mytable
where MyNumber not like '%[^0-9.]%'

Madhivanan

Failing to plan is Planning to fail

jasscat
Starting Member

9 Posts

Posted - 2009-12-24 : 09:42:38

This worked for me:

select [My Number] =
CASE IsNumeric(MyNumber)
WHEN 1
THEN (CAST(MyNumber as float)/100)
ELSE 0
END
from MyTable

I don't really need to cast it back to CHAR but that would be easy enough to do.

Thanks for the suggestions!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-28 : 00:45:25

quote:
Originally posted by jasscat

This worked for me:

select [My Number] =
CASE IsNumeric(MyNumber)
WHEN 1
THEN (CAST(MyNumber as float)/100)
ELSE 0
END
from MyTable

I don't really need to cast it back to CHAR but that would be easy enough to do.

Thanks for the suggestions!

Why didn't you use my suggestion?
Your method would throw error



select [My Number] =
        CASE IsNumeric(MyNumber)
              WHEN 1
              THEN (CAST(MyNumber as float)/100)
              ELSE 0
       END
from 
(
select '12d3' as Mynumber 
union all
select '345.23' 
union all
select '$378,23' 
)
MyTable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources