Fixed length output from numeric column

Question

Post reply

Fixed length output from numeric column

kalyan sankar

SSC Enthusiast

Points: 102
More actions
December 2, 2009 at 10:18 am

#214427

Hello experts
I have a numeric(9,4) columnA and the users need an output like below.
COLUMNA Modifier RESULT
1234.5678 10000 12345678
1.8 10 18
0.6750 10000 6750
The result * modifier should give the original values (column A)
Thx
Kalyan

Viewing 15 posts - 1 through 15 (of 18 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you can select the results of any calculation as a column in your query.

SELECT

COLUMNA ,

Modifier,

(COLUMNA * Modifier) As Result

from MyTable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kalyan sankar SSC Enthusiast Points: 102 More actions · Answer 2

I must state my request clearly:(

The columns Modifier and result are not in the table,I need to create those as output.

The table only has ColumnA.

Basically given any input with upto 4 decimal places I need an output without decimal and a modifier which can be applied to the RESULT to get back the original ColumnA values.

If ColumnA has 7.89,I need RESULT=789(i.e without decimal) and a modifier = 100 and hence to get ColumnA I do RESULT/Modifier=789/100=7.89.

HTH

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

kalyan sankar

I have a numeric(9,4) columnA

If you insert 7.89 / 1.8 into a numeric(9,4) column they will exist in the table as 7.8900 / 1.800

CREATE TABLE #T(ColumnA Numeric(9,4))

INSERT INTO #T

SELECT 1234.5678 UNION ALL

SELECT 1.8 UNION ALL --as specified in your original post

SELECT 0.6750 UNION ALL --as specified in your original post

SELECT 7.89 --as specified in your 2nd posting

SELECT * FROM #T

Tables contents

ColumnA

1234.5678

1.8000

0.6750

7.8900

Do I understand that you want the trailing 0 on 0.6750 to remain, but the trailing 00 to be trimed from the 7.89 input? Or all trailing zeroes to be removed?

Also

are these values to be used in another mathematical operation or would displaying them as VARCHAR be acceptable?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

kalyan sankar SSC Enthusiast Points: 102 More actions · Answer 4

Trailing zeroes are fine and the output will be in VARCHAR columns

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

kalyan sankar (12/2/2009)
I must state my request clearly:(
The columns Modifier and result are not in the table,I need to create those as output.
The table only has ColumnA.
Basically given any input with upto 4 decimal places I need an output without decimal and a modifier which can be applied to the RESULT to get back the original ColumnA values.
If ColumnA has 7.89,I need RESULT=789(i.e without decimal) and a modifier = 100 and hence to get ColumnA I do RESULT/Modifier=789/100=7.89.
HTH

You did well in stating your request and it's a piece-o-cake... 😉

--COLUMNA Modifier RESULT

--1234.5678 10000 12345678

--1.8 10 18

--0.6750 10000 6750

--===== Create a test table from the original posting.

-- This is NOT a part of the solution

DECLARE @t TABLE (ColumnA DECIMAL(9,4))

INSERT INTO @t (ColumnA)

SELECT 1234.5678 UNION ALL

SELECT 1.8 UNION ALL

SELECT 0.6750

--===== Show what's in the test table.

-- Again, this is NOT a part of the solution.

SELECT * FROM @t

--===== Now, solve the problem easily by using CTE's to peel one potato at a time...

;WITH

cteTrim

AS ( --=== Determine how many significant digits to right of decimal point

SELECT ColumnA,

Trimmed = RTRIM(REPLACE(CAST(ColumnA AS VARCHAR(10)),'0',' '))

FROM @t)

,

cteModifier

AS ( --=== From the above, determine what the modifier is

SELECT ColumnA,

Modifier = POWER(10,LEN(Trimmed)-CHARINDEX('.',Trimmed))

FROM cteTrim)

--===== From that, determine the final output

SELECT ColumnA,

Modifier,

Result = CAST(ColumnA*Modifier AS BIGINT)

FROM cteModifier;

Results...

ColumnAModifierResult

1234.56781000012345678

1.80001018

0.67501000675

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

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 6

Nice one Jeff,

Once again, you've proven we don't need RBAR code to solve even a format problem for SQL.

Keep it up, love to learn from you,

Cheers,

J-F

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

J-F Bergeron (12/2/2009)
Nice one Jeff,
Once again, you've proven we don't need RBAR code to solve even a format problem for SQL.
Keep it up, love to learn from you,

You've made my day, J-F. Thank you for the feedback!

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 8

Nice solution, but not entirely correct, I think. What about non-trailing zeroes behind the decimal point i.e. 1234.5078. The result is Modifier=1000, Result=1234507. I little fix will do:

SELECT ColumnA,

Trimmed = REPLACE(RTRIM(REPLACE(CAST(ColumnA AS VARCHAR(10)), '0', ' ')), ' ', '0')

But maybe that was Jeff's intention already (because of the RTRIM in the initial solution).

Peter

kalyan sankar SSC Enthusiast Points: 102 More actions · Answer 9

Thank you all very much.

That works for me and we will surely be using and testing the code in our application.

Did I mention This is the best support forum:-D:-D

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Peter Brinkhaus (12/3/2009)
Nice solution, but not entirely correct, I think. What about non-trailing zeroes behind the decimal point i.e. 1234.5078. The result is Modifier=1000, Result=1234507. I little fix will do:
SELECT ColumnA,
Trimmed = REPLACE(RTRIM(REPLACE(CAST(ColumnA AS VARCHAR(10)), '0', ' ')), ' ', '0')
But maybe that was Jeff's intention already (because of the RTRIM in the initial solution).
Peter

That last part is correct. The intent of the first CTE in my example has nothing to do with producing the correct number on the first pass. The only purpose was to find the last significant digit so we can count the number of places past the decimal point. That number of places was then used to figure out the necessary power of 10 to create the multiplier for. Because the CTE's "reflect" back into each other, it makes for some very high speed code that's also easy to read/debug because of the Divide'n'Conquer nature of the code.

I'll take a look and see where I mucked up on the embedded zero.

--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: 1004339 More actions · Answer 11

Ah... now I see. Somewhere along the line, a space in quotes got dropped when I transferred the code to the forum window. I've edited the previous code to be correct.

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 12

Now I see too. Replacing the spaces back to zeroes after trimming isn't necessary as the Trimmed value is only used to determine the number of digits behind the decimal point. Again, nice solution.

Peter

matzer SSCrazy Eights Points: 9727 More actions · Answer 13

Jeff Moden (12/3/2009)
Ah... now I see. Somewhere along the line, a space in quotes got dropped when I transferred the code to the forum window. I've edited the previous code to be correct.

Just another thought. Wouldn't it be a better idea to publish the modified code in a new post (even if the change is just a single space)? In general, I think, threads will become hard to follow when people start to edit their posts based on comments in other posts within the same thread.

Peter

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

I thought of doing that, as well. But if someone stops at the first post (as so often happens), they'd have bad code.

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