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]
-----------------------------------------------------------------------------