CODEWITHSUNDEEP
androidsqlite
Femn Dharamshi
Femn Dharamshi

Reputation: 577

Filtering out data for sql android

I have a strings that contains c++ codes.

these codes might contain a single or double inverted quotes and many such thing,

I want to filter out these characters before executing the sql to insert this into the SQLite Database (Android) so, what java code should i run to do that without disturbing/distorting the c++ code, so that when i read the sql database the code should be as before.

Upvotes: 0

Views: 411

Answers (1)

MikeT
MikeT

Reputation: 57043

You could filter (replace with nothing) when extracting the data using SQL.

e.g. such a query could be :-

SELECT replace(replace(col1,'''',''),'"','') FROM cpluspluscode;
  • where the respective column is col1 and the table is cpluspluscode.

The following is an example showing how this works:-

DROP TABLE IF EXISTS cpluspluscode;
CREATE TABLE IF NOT EXISTS cpluspluscode (col1 TEXT);
INSERT INTO cpluspluscode VALUES('''mytext'' "other text"');
SELECT * FROM cpluspluscode;
SELECT replace(replace(col1,'''',''),'"','') AS filtered FROM cpluspluscode;

The results from the above are :-

Without filtering :-

enter image description here

Filtered :-

enter image description here

  • The above takes advantage of the SQLite replace core function replace(X,Y,Z)

Unicode

If you wanted the to do the above using unicode then you could use :-

SELECT replace(replace(col1,char(0034),''),char(39),'') AS filtered FROM cpluspluscode;
  • This utilises the SQLite char core function (see link above).
  • The unicode core function can be used to find the unicode for a character (again see link above).

Android Example

Assuming a subclass of SQLiteOpenHelper is DatabaseHelper and this creates the table as per :-

public static final String TABLE_CPLUSPLUSCODE = "cpluspluscode";
public static final String COLUMN1 = "col1";

.........

@Override
public void onCreate(SQLiteDatabase db) {
    String crtcpp = "CREATE TABLE IF NOT EXISTS " + TABLE_CPLUSPLUSCODE + "(" +
            COLUMN1 + " TEXT" +
            ")";
    db.execSQL(crtcpp);
}

And DatabaseHelper includes the methods :-

public long cppInsert(String value) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(COLUMN1,value);
    return db.insert(TABLE_CPLUSPLUSCODE,null,cv);
}

public Cursor getFiltered() {
    SQLiteDatabase db = this.getWritableDatabase();
    String[] columns = new String[]{"replace(replace(" + COLUMN1 + ",'''',''),'\"','') AS " + COLUMN1};
    return db.query(TABLE_CPLUSPLUSCODE,columns,null,null,null,null,null);
}

public Cursor getUnfiltered() {
    SQLiteDatabase db = this.getWritableDatabase();
    return db.query(TABLE_CPLUSPLUSCODE,null,null,null,null, null, null);
}

Then using the following (in an Activity) :-

    DatabaseHelper mDBHlp = new DatabaseHelper(this);

    mDBHlp.cppInsert("''mydata'' \" other data\"");
    Cursor csr1 = mDBHlp.getUnfiltered();
    while (csr1.moveToNext()) {
        Log.d("CSR1DATA",csr1.getString(csr1.getColumnIndex(DatabaseHelper.COLUMN1)));
    }
    csr1.close();
    Cursor csr2 = mDBHlp.getFiltered();
    while (csr2.moveToNext()) {
        Log.d("CSR2DATA",csr2.getString(csr2.getColumnIndex(DatabaseHelper.COLUMN1)));
    }

Results in :-

09-05 04:39:14.003 3471-3471/so52115977.so52115977 D/CSR1DATA: ''mydata'' " other data"
09-05 04:39:14.003 3471-3471/so52115977.so52115977 D/CSR2DATA: mydata  other data

i.e. the second line is filtered accordingly.

Upvotes: 1

Related Questions