asked
show decimal value in Varchar
I have a requirement like in the target table the Balance is in Varchar as it has to show negative sign coming from the source.but they want the decimal places to show in the target table.For example if the source is(10000-),my target should show 100.00-I am not sure how to convert this.any thoughts?
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question. You can mark your own answer as the solution.
First, I feel compelled to point out that SQL is not "supposed" to be used as a presentation formatter. That said, there are ways to do what you are asking. I noticed you put the negative sign on the right, but I don't know if that's just a locale/culture setting. Regardless, the easiest way to do this is to use the FORMAT function available in SQL Server 2012 and newer. The first FORMAT of each SELECT below assumes the location of the minus sign is culture-based; the second forces it to be at the end. DECLARE @balance decimal(19,6) = -123456789.789123; SELECT FORMAT(@balance, '0.00'), FORMAT(@balance, '0.00;0.00-'); SET @balance = 0; SELECT FORMAT(@balance, '0.00'), FORMAT(@balance, '0.00;0.00-');
My source column and Target Columns are Varchar.and the negative sign supposed to be at the end.(values in the columns are like(00001224,00001235-,00002222)like that not all the values has -(negative) sign, but the negative sign is at the end. Your code didn't work for me and the format function is throwing error.Thank you though
I have a few questions, and then I'm sure we can solve this. 1) What version of SQL Server are you running? 2) Can you respond with the 3 examples you mentioned earlier along with the expected result for each? 3) Can you be more specific When you say "in SSIS"? Are you using a data flow? Can the solution be in T-SQL in a data flow source?
Yes .... The solution is in data flow task but the source and destination are in varchar ....and as I mentioned if my source values are (0001234,result should be 12.34,if it is 0001235-, result: 12.35-,source value: 025000-, result:250:00- something like that o used replace, cast and cast but getting errors
I got the solution to my problem,Here it is: SELECT LTRIM(RTRIM(CASE WHEn CHARINDEX('-',[ballance]) >0 THEN Convert(VARCHAR(200),(CAST((Replace([ballance],'-',' ' )) AS Float)/100))+ '-' ELSE Convert(VARCHAR(250),(CAST([ballance] AS Float)/100))END)) AS Ballance FROM [dbo].[CheckData]
19 People are following this question.
