August 19, 2013 at 10:17 am
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
August 19, 2013 at 10:36 am
Try removing the - that's between THEN and CONVERT.
Does that work for you?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 19, 2013 at 10:37 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2013 at 10:39 am
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. :w00t:
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 19, 2013 at 10:40 am
No need to reinvent the wheel, you just need the CONVERT
SELECT CONVERT(decimal (18,2),TestData)
FROM #Test
August 19, 2013 at 10:42 am
I need more coffee. 😎
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 19, 2013 at 11:39 am
Thanks. I see now, the CONVERT does it all.
August 19, 2013 at 7:11 pm
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 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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply