T&B wrote:
Hi all,
I have some imported data, where some fields contain numbers with
commas denoting thousands separators. How can I change these to actual
numbers?
I tried using CAST, which only works with later SQLite versions, but it
doesn't seen to know the comma as the thousands marker. For instance:
sqlite> SELECT CAST('1,234,567' AS REAL);
1.0
As an aside, the imported field data also starts with a dollar sign in
most cases. Is this the best way to get rid of it:
CREATE TABLE Sample( Cost );
INSERT INTO Sample VALUES('$1,234,567');
INSERT INTO Sample VALUES('1,000,000');
SELECT CASE WHEN Cost LIKE '$%' THEN substr( Cost, 2, length( Cost ) -
1 ) ELSE Cost END FROM Sample;
which gives:
1,234,567
1,000,000
Thanks,
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
How about adding a function which parses your string and transforms it
into the numeric format of your choice.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
