remove format of data 2

[Platinumsystemsltd]

i have a table which has prices in it, i'm now making a view of that table but in my view i want the decimal place and the currency sign to be taken away any help.

 

[SkipVought]

Hi,

When you say that you "...want the decimal place and the currency sign to be taken away ..." do you mean

a) you want an implied decimal or

b) you want ONLY the integer portion of the value?



Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[Platinumsystemsltd]

I want only the interger of the decimal for example
i have 12.99 i want 1299. Please help.

 

[JarlH]

Check out the SQL functions SUBSTRING, TRIM, and/or CAST.

For example, if your original data is
"$14.99", "$143.99", and "$ .73"

You can use SUBSTRING to remove the leading (currency) character:
SUBSTRING(price FROM 2) will give you
"14.99", "143.99", " .73"


Then use CAST to change to decimal data type:
CAST(SUBSTRING(price FROM 2) AS decimal(10,2)) will give you
14.99, 143.99, and 0.73

Then multiply by 100 and perhaps CAST the result as integer.

But the question is really why do you store currency characters together with price in the same column? Consider one column for price and one column for currency. Besides numeric data types are pretty good for numeric data.

 

[SkipVought]

I would NOT use STRING FUNCTIONS on a number.

Simple! Multiply all your price values by 100.

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[Platinumsystemsltd]

Thanks Guys that was great

 

[r937]

maybe ANSI SQL has some datatype with currency symbols built in, but i'm guessing this isn't one of those situations, it's either a proprietary datatype like micro$oft money, in which case just multiplying by 100 won't remove the symbol, or it's a varchar, in which case string functions are a must

in this instance i'm going to have to go with the string functions as the probable answer



r937.com | rudy.ca