Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to write query Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 11:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 07, 2013 4:54 AM
Points: 64, Visits: 204
Hello

I have to generate a report in which amount should be 13 digits.

e.g 2000- convert it into paisa 200000 then 13 digit amount is 0000000200000

but I want it to do in SQl query..

Select (amount*100) from table
then need to add 0s as per count 13-len(amount*100) before the amount

please guide me..
Post #1490005
Posted Thursday, August 29, 2013 11:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 07, 2013 4:54 AM
Points: 64, Visits: 204
I got the solution

Use Replicate function
SELECT '200000'
SELECT REPLICATE('0',7)+'200000'
Post #1490007
Posted Friday, August 30, 2013 3:59 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
SELECT replace(str(amt, 13), ' ', '0')



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490324
Posted Friday, August 30, 2013 11:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:29 PM
Points: 35,977, Visits: 30,266
If you're interested in speed, STR isn't the way to do it. And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits). Then there's the rounding errors that it produces because of the approximations it does.

My recommendation is to avoid STR().


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1490352
Posted Friday, August 30, 2013 11:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:29 PM
Points: 35,977, Visits: 30,266
amitsingh308 (8/29/2013)
Hello

I have to generate a report in which amount should be 13 digits.

e.g 2000- convert it into paisa 200000 then 13 digit amount is 0000000200000

but I want it to do in SQl query..

Select (amount*100) from table
then need to add 0s as per count 13-len(amount*100) before the amount

please guide me..


One of the fastest ways of doing such a thing is with a little hardcoding. You might want to avoid things like REPLICATE to calculate a certain number of concatenated zero's because its just not as fast as a string literal. You should have good luck with the following...
 SELECT RIGHT('0000000000000' + CAST((Amt*100) AS VARCHAR(13)),13)
FROM dbo.SomeTable
;


Of course, such formatting should probably be done in the front-end, if there is one.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1490354
Posted Saturday, August 31, 2013 3:10 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
Jeff Moden (8/30/2013)
If you're interested in speed, STR isn't the way to do it.


It's unlikely that the speed difference would be significant. Yes, if you need to do this on 10 million rows on straight SELECT from a single table, you might see something. If you instead query umpteen tables with a suboptimal query plan, this will be one your smallest problem.

And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits).


True, the argument will be converted to float, and this could pose problems with you are working with 16-digit bigint numbers you want to pad to 18 digits. An integer value is typically converted to float with the exponent as 0, meaning that there is no loss of precision.

I like str() + replace() because is short and concise.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490363
Posted Saturday, August 31, 2013 10:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:29 PM
Points: 35,977, Visits: 30,266
Erland Sommarskog (8/31/2013)
Jeff Moden (8/30/2013)
If you're interested in speed, STR isn't the way to do it.


It's unlikely that the speed difference would be significant. Yes, if you need to do this on 10 million rows on straight SELECT from a single table, you might see something. If you instead query umpteen tables with a suboptimal query plan, this will be one your smallest problem.

And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits).


True, the argument will be converted to float, and this could pose problems with you are working with 16-digit bigint numbers you want to pad to 18 digits. An integer value is typically converted to float with the exponent as 0, meaning that there is no loss of precision.

I like str() + replace() because is short and concise.


I used to think the same thing for a very long time. Like me, you might want to change you mind.

It may be "concise" but it's far from accurate and far from being performant.

It produces rounding errors when working with decimal places simply because FLOAT is based on binary math rather than decimal math and it can't actually precisely contain certain values with it's rather short Mantissa even on small numbers.

STR() is also more than twice as slow as RIGHT() concatenation method. While I agree that a 2,300ms difference in CPU time (3,800ms vs 1,500ms) for a single instance of the formula over a million rows on a relatively slow machine isn't much, just imagine if everyone on a project paid attention to such minor nuances... the end result would be code that ran twice as fast as the compeition's code. And, what does it cost? If you know how to use the RIGHT() concatenation method and even if you can't actually type quickly, the answer is measured as just a second or two to get twice the performance and much more accurate rounding.

Here's a link for some testing that I did that demonstrates the performance and accuracy differences. Again, it was a while back meaning that I did the testing on my really old desktop and that causes the appearance of a larger difference in time but that doesn't affect things like the rounding errors when working with decimal numbers.
http://www.sqlservercentral.com/articles/T-SQL/71565/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1490401
Posted Saturday, August 31, 2013 12:08 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:43 AM
Points: 756, Visits: 631
Jeff Moden (8/31/2013)
[quote][b]It produces rounding errors when working with decimal places simply because FLOAT is based on binary math rather than decimal math and it can't actually precisely contain certain values with it's rather short Mantissa even on small numbers.


That assumes decimal values. The case in point was numeric values. If you want to use str() to get right-aligned decimal values, yes, you can get errors, and if it essential that your rounding is consistent there may be a workaround:

SELECT str(1.23955, 10, 4) AS Wrong, convert(float, 1.23955) AS Why, 
str(round(1.23955, 4), 10, 4) AS Workaround

Note: you need to run the query from SQLCMD to see Why.

STR() is also more than twice as slow as RIGHT() concatenation method.


Frankly, I couldn't be bothered. I also use things like:

convert(char(6), getdate(), 112) + '01'

when I want the first day of the month, even if people claim other constructs are faster. I look at them and say, eh? I can do the most common date tricks with style 112 in my sleep.

Developer productivity counts some times. Performance in T-SQL is about greater scheme of things.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490412
Posted Saturday, August 31, 2013 12:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:29 PM
Points: 35,977, Visits: 30,266
I don't need to run it from SQLCMD to see why. I've already demonstrated "Why" in the article.

So far as "Developer Productivity" goes, I'm all for that. That's why I teach my Developers to learn the more performant methods... so they can do it in their sleep and won't have to rewrite code to fix performance problems that should have been avoided to begin with.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1490415
Posted Wednesday, September 11, 2013 9:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:35 PM
Points: 3,593, Visits: 5,102
Another way that should look familiar:

WITH Amt (Amt) AS (SELECT 2000)
SELECT RIGHT(10000000000000+Amt*100,13)
FROM Amt;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1493984
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse