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 |
|
caravanpunk
Starting Member
20 Posts |
 Posted - 2011-05-11 : 19:29:00
|
| Dear frens, I want to change percentage(10%) for example to 010 value. 50% to 050 How can I do that?Thank You in advance |
|
|
orashid
Starting Member
3 Posts |
Posted - 2011-05-11 : 19:54:35
|
quote:
Originally posted by caravanpunk
Dear frens, I want to change percentage(10%) for example to 010 value. 50% to 050 How can I do that?Thank You in advance
Try thisselect substring(Cast((50*0.001) as nvarchar),charindex('.',cast((50*0.001) as nvarchar),1)+1,len(cast((50*0.001) as nvarchar)))This is for 50%For 2.9 % useselect substring(Cast((2.9*0.001) as nvarchar),charindex('.',cast((2.9*0.001) as nvarchar),1)+1,len(cast((2.9*0.001) as nvarchar))) |
 |
|
|
caravanpunk
Starting Member
20 Posts |
Posted - 2011-05-11 : 20:01:00
|
| Thank you very much, was desperately waiting for the answerthank you |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-11 : 20:04:05
|
Assuming that your original data is stored as varchar, it would be shorter and perhaps even more readable to do it like this:declare @p varchar(32);set @p = '50%';select right('000'+replace(@p,'%',''),3);Now having said that, many people on this forum would advise you to keep numbers in a numeric data type (such as int, decimal etc.) rather than as string, and to do the formatting (such as padding with zeros) at the client (for example at the presentation layer).There are some very valid reasons for that advice, but sometimes you just have to do what you have to do even AMA. |
|
|
|
orashid
Starting Member
3 Posts |
Posted - 2011-05-11 : 20:08:52
|
quote:
Originally posted by sunitabeck
Assuming that your original data is stored as varchar, it would be shorter and perhaps even more readable to do it like this:declare @p varchar(32);set @p = '50%';select right('000'+replace(@p,'%',''),3);Now having said that, many people on this forum would advise you to keep numbers in a numeric data type (such as int, decimal etc.) rather than as string, and to do the formatting (such as padding with zeros) at the client (for example at the presentation layer).There are some very valid reasons for that advice, but sometimes you just have to do what you have to do even AMA.
Can you please try your code for 2.9%? It worked fine for 50%. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-11 : 20:23:59
|
What I posted assumed that caravan's percentages were all integer numbers. If it is not, not clear what the desired result is. Whether it is like:50% --> 0502.9% --> 0029or50% --> 05002.9% --> 0290 In either case, obviously more work will need to be done, like you did. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-05-12 : 02:55:49
|
quote:
Originally posted by caravanpunk
Dear frens, I want to change percentage(10%) for example to 010 value. 50% to 050 How can I do that?Thank You in advance
What is the datatype of the column?Where do you want to show this formatted data?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Change % to numeric