March 18, 2011 at 3:03 am
Hello, i've got a small problem converting an varchar column to a numeric
i have a column a with a couple of records also negative records like '1.450,40' ,'142,07','83,71-'
how to convert this?
I've tried this
WHEN CHARINDEX('-', ColumnA) > 0 THEN CAST(Replace(Replace(ColumnA, '-', ''), ',', '') as numeric(9, 2)) * -1
ELSE CAST(Replace(ColumnA, ',', '') as numeric(10, 2))
but then the results are like this and i don't want that !
ColumnA (No column name)
1.450,40 1.45
142,07 14207.00
83,71- -8371.00
March 18, 2011 at 3:46 am
Leon, what you wrote in SQL and what you see makes sense to me.
What do you WANT to see for those items?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 3:49 am
I want to convert a varchar column to a numeric(9,2) column
but the vachar column has also negative values
ColumnA
1.450,40
142,07
83,71-
March 18, 2011 at 4:07 am
leon.booij (3/18/2011)
I want to convert a varchar column to a numeric(9,2) columnbut the vachar column has also negative values
ColumnA
1.450,40
142,07
83,71-
So, you're expecting to see:
1.450,40
142,07
-83,71
?
That's not going to work in a decimal.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 4:09 am
yes a want to see that, is that possible
March 18, 2011 at 4:16 am
leon.booij (3/18/2011)
yes a want to see that, is that possible
Not from decimal, no.
You are in varchar, and need to stay in varchar. You're looking at this the wrong way for what you want (which I'm not going to discuss how viable that is from a usability standpoint). You have a character at the tail of the varchar that you want at the beginning if it exists.
UPDATE Table
SET field = CASE WHEN right( field, 1) = '-'
THEN '-' + LEFT( field, len( Field) - 1)
ELSE field
END
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 4:20 am
Thnx i will try that
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply