Your question made me want to do something I've been meaning to for a while...a
custom function.
So...the following words as loadable extension (at least for the shell)
gcc -g -shared -fPIC -o sortprds.sqlext sortprds.c
sqlite3 test.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a(ID,prdtype);
INSERT INTO "a" VALUES(1,'15');
INSERT INTO "a" VALUES(2,'15|10|27|3');
INSERT INTO "a" VALUES(2,'8|6|22');
COMMIT;
sqlite> .load './sortprds.sqlext'
sqlite> select sortprds(prdtype) from a order by sortprds(prdtype);
3| 10| 15| 27
6| 8| 22
15
Maybe somebody else can point out my boo-boos in here...in particular if you
enable the MAIN you should be able to do this:
gcc -DMAIN -g -o sortprds sortprds.c sqlite3.c -lpthread -ldl
./sortprds test.db "select * from a"
But it segfaults on me at sqlite3_open() and I can't figure out why...there's
no stack info.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqlite3.h"
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#define MAXLEN 4096
static int
cmpintp(const void *p1, const void *p2) {
if (*(int*)p1 < *(int*)p2) {
return -1;
}
if (*(int*)p1 > *(int*)p2) {
return 1;
}
return 0;
}
static void sortprdsFunc(sqlite3_context *context,
int argc,
sqlite3_value **argv ) {
int i,c,*data;
char *buf=malloc(MAXLEN);
char *p;
// count the terms
strncpy(buf,sqlite3_value_text(argv[0]),MAXLEN-1);
buf[MAXLEN]=0;
for(i=0,c=1; buf[i]!=0; ++i) {
if (buf[i]=='|') ++c;
}
data=malloc(c*sizeof(int));
for(i=0,p=buf; i<c; ++i) {
sscanf(p,"%d",&data[i]);
p=strchr(p,'|')+1;
}
qsort(data,c,sizeof(int),cmpintp);
buf[0]=0;
for(i=0; i<c; ++i) {
char n[32];
if (i==0) {
sprintf(n,"%6d",data[i]);
} else {
sprintf(n,"|%6d",data[i]);
}
strcat(buf,n);
}
sqlite3_result_text(context,buf,MAXLEN-1,SQLITE_TRANSIENT);
free(data);
free(buf);
}
int sqlite3_extension_init(sqlite3 *db,
sqlite3_value **pzErrMsg,
const sqlite3_api_routines *pApi) {
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "sortprds", 1, SQLITE_ANY, 0, sortprdsFunc, 0, 0);
return 0;
}
#ifdef MAIN
int main(int argc, char **argv) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
if( argc!=3 ) {
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
return(1);
}
rc = sqlite3_open(argv[1], &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(1);
}
rc = sqlite3_exec(db, argv[2], NULL, 0, &zErrMsg);
if( rc!=SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db);
return 0;
}
#endif
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: [email protected] [[email protected]] on
behalf of guiz [[email protected]]
Sent: Tuesday, January 03, 2012 11:15 AM
To: [email protected]
Subject: EXT :[sqlite] How to sort within a text field
Hi all, if I have a table like
CREATE TABLE [a] (
[ID] INTEGER NOT NULL,
[prdtype] VARCHAR(35) NOT NULL
)
and data already inserted as
insert into a (ID, prdtype) values (1, '15');
insert into a (ID, prdtype) values (2, '15|10|27|3');
insert into a (ID, prdtype) values (3, '8|6|22');
and I'd like to update table 'a' to achieve sorted result in prdtype as
ID prdtype
1 15
2 3|10|15|27
3 6|8|22
given that each sub-prdtype may be represented as only single or double
digit and separated by '|' character also each prdtype may have up to 8 sub
prdtype, i.e. '2|21|27|28|33|40|43|55'
I really appreciate your help on this if you could. Thank's.
Rick
--
View this message in context:
http://old.nabble.com/How-to-sort-within-a-text-field-tp33073347p33073347.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
