.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

decimal after 2 or 3 digit

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server
 

Table 1

col1

loan amt dec 10 Eur 12400

loan amt dec 10 t bond 12345

loan amt nov 20 t call 9975

loan amt 20t call 95250

I have to retrieve amount from above col like 12400 ,12345 and 9975 .I know the code for this like

select substring(col1,len(' ')-charindex(' ',reverse(col1))+Len(col1)+1,Len(col1)) from table1

There is space after every word in col1.

I am getting

12400

12345

9975

95250

Requirement is change I want decimal now if first digit is 1 than I have to put decimal after 3 digit ,if first digit is other than 1 than I have to put decimal after 2 digit

 I want output like this like this

124.00

123.45

99.75

95.250

If you know about this please let me know.Thanks in advance

 

 


isb123


View Complete Post


More Related Resource Links

decimal value validation and round off by javascript

  
The article decimal value validation and round off by javascript was added by hiral.hapani on Wednesday, June 02, 2010.

Hi All, I have createdone javascript by which you canvalidate a decimal value of any textbox for which you want. By thisjavascriptwecan bind user to enter only digits in textbox. and when he will enter any decimal value javascript will do round off it

Which one is better: Money or Decimal?

  

Hi

I want a field to keep money value (up to 100,000,000.00). And I didn't  work with money or decimal data type.

Money data Type is 8 byte

Decimal (9, 2) is 5 byte. (Exactly msdn only say Decimal (9, 0) is 5 byte and I don't really know how byte is Decimal (9, 2))

Please tell me which one is better to work with in sql and VS.net (handling sql in VS)

Best Regards. Morteza


Need RegEx for four decimal place

  
Hello All, I have total 19 digits in which i have 4 decimal place. What would be the pattern. ThanksBizTalk Beginner

How do I get Decimal.Parse to stop taking current culture into account?

  
How do I get Decimal.Parse to stop taking current culture into account?   I tried the following but I get an error saying “String was not recognized as a valid DateTime.   Thread.CurrentThread.CurrentCulture = new CultureInfo("en-us");   Decimal.Parse("5000.000,51",CultureInfo.InvariantCulture)

Bianry data to decimal value in sql

  
Hi all! can any one tell me please if we have data in one column is in binary format i.e  e.g 0111 result should be 7. How should we retrive the decimal value 7 through sql server 

Validate input has up to 2 decimal places

  
I need to validate hours put into a textbox.  I want to allow hours to be from 0-24 and allow up to 2 decimal places. so it has to accept 00.001.52.25but not 2.225 or 25I put a range validator that check that the value is from 0-24 but it doesn't check that there are 2 decimal places or less.  how do I do that?

Quick way to widen a decimal column in place?

  
Hi all, I have a table with over 400 million rows in it, and it turns out that one of the columns that was defined as decimal(14,5) needs to be widened to decimal(18,5).  The column is not used an any keys or indexes. What's the fastest way of doing that?  When I attempt to change the column definition (right-click the table in SSMS, select Design, update the column definition, then attempt to save), SSMS complains, saying Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.... Thanks, Matthew  

Loading Decimal Number From Excel into SQL Server 2005 using SSIS Package

  
If you have an excel spreadsheet that displays a number .02 (because it is formated) but internally it is stored as .2654329 and you want to load .2654329 how can you do that in the SSIS package?  When we load the spreadsheet it is loading .02.  Is there some kind of option in the SSIS package to tell the package to load the internal number?lcerni

Quick way to widen a decimal column in place?

  
Hi all, I have a table with over 400 million rows in it, and it turns out that one of the columns that was defined as decimal(14,5) needs to be widened to decimal(18,5).  The column is not used an any keys or indexes. What's the fastest way of doing that?  When I attempt to change the column definition (right-click the table in SSMS, select Design, update the column definition, then attempt to save), SSMS complains, saying Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.... Thanks, Matthew  

Entity Framework - Allow Dollar Signs for Decimal Types

  
Using ASP .Net 4.0,Entity Framework and EntityDataSource. I have some currency fields that are defined as Decimal. I would like to allow the user to enter a (leading) dollar sign in these (TextBox form fields). Although, when the EntityDataSource goes to save the changes to the datbase, it throws: Error while setting property 'QuotedMaterial': 'Cannot convert the value of parameter 'QuotedMaterial' to the type 'System.Decimal'.'. Does anyone know how I can "tell" the EF to allow dollar signs and convert (drop) them accordingly? I am trying to avoid creating my own Data or Business Logic layer for this particular application. I would like to stick with the EntityDataSource and other "built in" controls to minimize the C# code behind as much as possible. I guess my only alternative is to deny $ (Dollar Signs) (and commas too for that mater, it doesn't like them either). The problem is I am porting a legacy application and I would like to keep it as close to the original as possible (and they are currently allowed to enter dollar signs in this application). You see, they use copy/paste for data entry quote often on this particular form and it just so happens that the source data (they are copy/pasting from) always has a $ in it...    

Javascript numbers only, & format it to Decimal

  
Hello Experts, I need some help guys ;( I want some javascript code that make sure that end user must enter only numbers on textbox (currency,number), no characters, also typed number will be formated to be decimal number, I mean if user type (22222), javascript should format the number to be (22,222.0) javascript function should be called while end user typing on Textbox. so if he enter character, then the character should be removed automatically. I read many posts that talked about this, but most of them are using the onkeypress(event), so if the user paste the text that code will not work. Please help me guys, I need this urgent

Sql Order by not working with decimal/money type columns

  
Error Message:- Cannot convert a char value to money. The char value has incorrect syntax. DECLARE @Order NVARCHAR(50) = 'asc', @OrderBy NVARCHAR(50) = 'Shape'SELECTtop 1 Row_Number() OVER(ORDER BY  CASE WHEN @Order IS NULL OR @Order = 'asc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice    --MONEY    WHEN @OrderBy = 'Carat' THEN [Weight]   --DECIMAL   END  END ASC,  CASE WHEN @Order = 'desc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice  --MONEY   WHEN @OrderBy = 'Carat' THEN [Weight]  --DECIMAL   END  

Retrieve month name and last 2 digit of yr from date column

  
Table contain one column [d_matur] [datetime] NULL 2014-06-20 00:00:00.000 2015-03-20 00:00:00.000 like this now I want to retrieve month name like June as well as last two digit of year like 14 if anyone know query how to get these from above column please let me know.Thanks

select last digit in sqlserver and check it ?

  
hi all i have this case RIGHT   (LTRIM(RTRIM(ListPrice2)), 1) = '0' this will get the last digit from ListPrice2 column i want to check if it equal ZERO then Remove it ? how can i do that in sql server ? please help !!!!

Unpack Decimal transformation in SSIS 2008?

  
Hi, In order to convert the packed fields, Microsoft has released Unpack Decimal transformation but it supports only SSIS 2005 version. I don't see a version that can support the SSIS 2008. Please let me know, as to how to handle these packed feilds using SSIS 2008. Regards, Tushar.

DECIMAL with dynamic parameters

  

I am trying to create a function to do the following formatting:

RIGHT(CONVERT(DECIMAL(@precision,@decimal),@number),@width)

Unfortunately DECIMAL is not a function and therefore does not take parameters it seems.

CREATE FUNCTION DigitFormat(@number as TEXT, @precision as INTEGER, @decimal as INTEGER, @width as INTEGER)
RETURNS VARCHAR(100)
BEGIN
declare @Return VARCHAR(100)
SET @Return = RIGHT(CONVERT(DECIMAL(@precision,@decimal),@number),@width)
return @Return
END
Is there a way to solve this? I tried using EXEC but got no where...

 

 


Decimal Point

  

How do i format the field so i just get 2 decimal point, this is how it is shown on the report currently

1.90000000
5.40000000
NULL
8.20000000
40.00000000
1.40000000

I want the table to look like this...

1.90
5.40

8.20
40.00
1.40

 


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend