how to write query

Question

Post reply

how to write query

amitsingh308

Ten Centuries

Points: 1287
More actions
August 29, 2013 at 11:38 pm

#279202

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..

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

amitsingh308 Ten Centuries Points: 1287 More actions · Answer 1

I got the solution

Use Replicate function

SELECT '200000'

SELECT REPLICATE('0',7)+'200000'

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

SELECT replace(str(amt, 13), ' ', '0')

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

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.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

Jeff Moden (8/31/2013)

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.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 9

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![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86948 More actions · Answer 10

BTW. I really like this.

Jeff Moden (8/31/2013)
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.

You can lead a horse to water but how the heck do you get the beast to drink?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

dwain.c (9/11/2013)
BTW. I really like this.
Jeff Moden (8/31/2013)
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.
You can lead a horse to water but how the heck do you get the beast to drink?

It takes a lot of patience but the answer is, hire only those horses that are willing to drink and make the water taste good. 😉

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 12

Jeff Moden (9/12/2013)
dwain.c (9/11/2013)
BTW. I really like this.
Jeff Moden (8/31/2013)
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.
You can lead a horse to water but how the heck do you get the beast to drink?
It takes a lot of patience but the answer is, hire only those horses that are willing to drink and make the water taste good. 😉

It seems all of the ones we hire foul the creek.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St