Re: [sqlite] how to get the records matching some two characters?
Thanks! This is what I need, :) Jay A. Kreibich-2 wrote: > > On Mon, Aug 31, 2009 at 06:33:08PM -0700, liubin liu scratched on the > wall: >> >> create table and index: >> CREATE TABLE data_rt ( id INTEGER, dataid CHAR(4), data CHAR(12), >> rec_time >> INTEGER, data_type CHAR(1) ); >> CREATE UNIQUE INDEX i_drt ON data_rt ( id, dataid ); >> >> there are data in the table of data_rt: >> sqlite> SELECT * FROM data_rt; >> 6|1290|7e22473a|857000|22 >> 7|1291|7e22473a|859000|22 >> 8|1190|7e22473a|861000|22 >> 9|1390|7e22473|862000|22 >> 8|1390|7e22473|861000|22 >> 7|1391|7e22473|86|22 >> 6|1391|7e22473|859000|22 >> 5|1391|7e22473|858000|22 >> >> >> I want to get the records that the dataid's two end characters is '90'. >> >> How to write the select words? > > As long as you're sure dataid is a four character string, and not > a number, you can use: > > SELECT * FROM data_rt WHERE dataid LIKE '__90'; > > See http://www.sqlite.org/lang_expr.html for specifics. You might > want to use '%90'. > > This will do a full table scan. > >-j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > :jumping::jumping: -- View this message in context: http://www.nabble.com/how-to-get-the-records-matching-some-two-characters--tp25233067p2526.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to get the records matching some two characters?
On Mon, Aug 31, 2009 at 06:33:08PM -0700, liubin liu scratched on the wall: > > create table and index: > CREATE TABLE data_rt ( id INTEGER, dataid CHAR(4), data CHAR(12), rec_time > INTEGER, data_type CHAR(1) ); > CREATE UNIQUE INDEX i_drt ON data_rt ( id, dataid ); > > there are data in the table of data_rt: > sqlite> SELECT * FROM data_rt; > 6|1290|7e22473a|857000|22 > 7|1291|7e22473a|859000|22 > 8|1190|7e22473a|861000|22 > 9|1390|7e22473|862000|22 > 8|1390|7e22473|861000|22 > 7|1391|7e22473|86|22 > 6|1391|7e22473|859000|22 > 5|1391|7e22473|858000|22 > > > I want to get the records that the dataid's two end characters is '90'. > > How to write the select words? As long as you're sure dataid is a four character string, and not a number, you can use: SELECT * FROM data_rt WHERE dataid LIKE '__90'; See http://www.sqlite.org/lang_expr.html for specifics. You might want to use '%90'. This will do a full table scan. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to get the records matching some two characters?
create table and index: CREATE TABLE data_rt ( id INTEGER, dataid CHAR(4), data CHAR(12), rec_time INTEGER, data_type CHAR(1) ); CREATE UNIQUE INDEX i_drt ON data_rt ( id, dataid ); there are data in the table of data_rt: sqlite> SELECT * FROM data_rt; 6|1290|7e22473a|857000|22 7|1291|7e22473a|859000|22 8|1190|7e22473a|861000|22 9|1390|7e22473|862000|22 8|1390|7e22473|861000|22 7|1391|7e22473|86|22 6|1391|7e22473|859000|22 5|1391|7e22473|858000|22 I want to get the records that the dataid's two end characters is '90'. How to write the select words? -- View this message in context: http://www.nabble.com/how-to-get-the-records-matching-some-two-characters--tp25233067p25233067.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users