SQLite TEXT CAST到INTEGER或C ++中的REAL太多了 [英] SQLite TEXT CAST to INTEGER or REAL in C++ Does too much
问题描述
我在 TEXT
列中输入数据类型时遇到问题。
我 HAVE 可上传自定义CSV文件中的所有数据。我不能保证我甚至得到所有的列,我希望更好的数据类型,所以我开始一个表,其中所有的列都是类型TEXT,并在空白的地方放空字符串。
表格如下所示
CREATE TABLE tbl1(col1 TEXT,col2 TEXT,col3 TEXT);
从文件加载 tbl1
后,我运行此操作。
SELECT * FROM tbl1;
选择结果:
'1','String1','2.0'
'2','String2','3.14'
'3','String3','6.77776'
'h','Stringh','h.h' / p>
'','String',''
现在我想从 tbl1
并使用它填充此表。
CREATE TABLE tbl2(col1 INTEGER,col2 TEXT, col3 REAL);
我尝试这样。
INSERT INTO tbl2 SELECT CAST(tbl1.col1 as INTEGER),tbl1.col2,CAST(tbl1.col3 AS REAL)FROM tbl1;
之后,我执行此操作,
SELECT * FROM tbl2;
< blockquote>
选择结果:
1,'String1',2.0
2,'String2',3.14
3,'String3',6.77776
0, Stringh',0
0,'String',0
我真正想要的是将会考虑好铸造并将它们插入
tbl2
然后取所有的值,我认为坏铸造,并把它们放在一个'tbl3' 。
CREATE TABLE tbl3(col1 TEXT,col2 TEXT,col3 TEXT,REASON_FOR_REJECTION TEXT);
< blockquote>
tbl3
将用于报告并可能对错误数据进行故障排除。
在我插入
tbl2
之前,我需要在C ++中预处理这些数据吗?或者SQLite支持某种查询功能,允许我捕获Bad
UPDATE:
通过将CL。的查询添加到结尾,我可以区分哪些记录Bad强制转换并清除
tbl2
,并将不良数据行添加到tbl3
。解决方案阅读SQLite的类型文档亲密度。
当您将列声明为
INTEGER
或REAL
,SQLite将自动尝试转换值。
任何无法转换的值将保留原始类型。
因此,只需将数据直接导入
tbl2
,然后查找具有以下查询的所有记录:INSERT INTO tbl3
pre>
SELECT col1,col2,col3,
trim(CASE typeof(col1)
WHEN'integer'THEN''
ELSE'col1:'|| typeof(col1)
END ||
''||
CASE typeof(col3)
WHEN'real'THEN''
ELSE'col3:'|| typeof(col3)
END)
FROM tbl2
WHERE typeof(col1)!='integer'
或typeof(col3)!='real'
I'm having trouble casting data types in
TEXT
columns.I HAVE to upload all data from a custom CSV file. There is no guarantee that I'll even get all the columns I expect let alone the right data types so I start with a table where all columns are of type TEXT and put empty strings in the blank spots.
Ex. Table looks like this
CREATE TABLE tbl1 ( col1 TEXT, col2 TEXT, col3 TEXT);
After loading
tbl1
from a file I run this.SELECT * FROM tbl1;
Select results:
'1','String1', '2.0'
'2','String2', '3.14'
'3','String3', '6.77776'
'h','Stringh', 'h.h'
'','String', ''
Now I want to take the data from
tbl1
and use it to populate this table.CREATE TABLE tbl2 ( col1 INTEGER, col2 TEXT, col3 REAL);
And I try it like this.
INSERT INTO tbl2 SELECT CAST(tbl1.col1 as INTEGER), tbl1.col2, CAST(tbl1.col3 AS REAL) FROM tbl1;
After that I run this,
SELECT * FROM tbl2;
Select results:
1,'String1', 2.0
2,'String2', 3.14
3,'String3', 6.77776
0,'Stringh', 0
0,'String', 0
What I really want is to grab what I would consider 'Good' casts and insert them into
tbl2
And then take all the values I would consider 'Bad' casts and put them in a 'tbl3' that would look like this.CREATE TABLE tbl3 (col1 TEXT, col2 TEXT, col3 TEXT, REASON_FOR_REJECTION TEXT);
tbl3
would be for reporting and possibly trouble shooting bad data.Do I need to preprocess this data in C++ before I insert it into
tbl2
or does SQLite support some kind of querying function that allows me to catch 'Bad' casts?UPDATE:
By adding CL.'s query to the end of this I could distinguish which records had "Bad" Casts and clean up
tbl2
and add bad data rows totbl3
.解决方案Read the SQLite documentation about type affinity.
When you declare a column as
INTEGER
orREAL
, SQLite will automatically try to convert values. Any values that cannot be converted will retain the original type.So just import your data directly into
tbl2
, then find all records with errors with a query like this:INSERT INTO tbl3 SELECT col1, col2, col3, trim(CASE typeof(col1) WHEN 'integer' THEN '' ELSE 'col1:' || typeof(col1) END || ' ' || CASE typeof(col3) WHEN 'real' THEN '' ELSE 'col3:' || typeof(col3) END) FROM tbl2 WHERE typeof(col1) != 'integer' OR typeof(col3) != 'real'
这篇关于SQLite TEXT CAST到INTEGER或C ++中的REAL太多了的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!