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

Convert character data to decimal Expand / Collapse
Author
Message
Posted Monday, August 19, 2013 10:17 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 643, Visits: 3,695
I have some data coming in with leading 0s as well a +/-.

You can see what I have tried so far. I don't understand why the negative numbers are not being handled correctly.

Thanks if you have a suggestion or a better way to do this.

CREATE TABLE #Test
(TestData varchar(50))

INSERT INTO #Test SELECT '+000000000000760.00'
INSERT INTO #Test SELECT '+000004532078501.60'
INSERT INTO #Test SELECT '-000000001224249.00'
INSERT INTO #Test SELECT '+000000000468181.00'


SELECT * FROM #Test

SELECT

CASE LEFT(TestData,1)
WHEN '+' THEN CONVERT(decimal (18,2),REPLACE(LTRIM(REPLACE(TestData,'0',' ')),' ','0'))
WHEN '-' THEN - CONVERT(decimal (18,2),REPLACE(LTRIM(REPLACE(TestData,'0',' ')),' ','0'))
END
FROM #Test

DROP TABLE #Test

Post #1485883
Posted Monday, August 19, 2013 10:36 AM


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: Monday, April 07, 2014 9:45 AM
Points: 3,089, Visits: 7,741

Try removing the - that's between THEN and CONVERT.

Does that work for you?




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1485896
Posted Monday, August 19, 2013 10:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 11,949, Visits: 10,982
No need for a case expression here at all. Just convert the value, it will give you the negative values just fine.

CONVERT(decimal (18,2),REPLACE(LTRIM(REPLACE(TestData,'0',' ')),' ','0'))



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1485899
Posted Monday, August 19, 2013 10:39 AM


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: Monday, April 07, 2014 9:45 AM
Points: 3,089, Visits: 7,741
Sean Lange (8/19/2013)
No need for a case expression here at all. Just convert the value, it will give you the negative values just fine.

CONVERT(decimal (18,2),REPLACE(LTRIM(REPLACE(TestData,'0',' ')),' ','0'))



Duh!!! I missed that.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1485900
Posted Monday, August 19, 2013 10:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 2,763, Visits: 5,901
No need to reinvent the wheel, you just need the CONVERT

SELECT CONVERT(decimal (18,2),TestData)
FROM #Test




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1485901
Posted Monday, August 19, 2013 10:42 AM


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: Monday, April 07, 2014 9:45 AM
Points: 3,089, Visits: 7,741

I need more coffee.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1485903
Posted Monday, August 19, 2013 11:39 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 643, Visits: 3,695
Thanks. I see now, the CONVERT does it all.
Post #1485925
Posted Monday, August 19, 2013 7:11 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:01 PM
Points: 3,590, Visits: 5,096
Just to be a little out of control (of the resulting type), you don't even need CONVERT:

CREATE TABLE #Test (TestData varchar(50));

INSERT INTO #Test SELECT '+000000000000760.00';
INSERT INTO #Test SELECT '+000004532078501.60';
INSERT INTO #Test SELECT '-000000001224249.00';
INSERT INTO #Test SELECT '+000000000468181.00';


SELECT TestData, 0E1+TestData
FROM #Test;

GO
DROP TABLE #Test;


Helps my Carpal Tunnel syndrome.



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 #1486084
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse