[sqlite] replace extra carriage returns?
I'm trying to clean out a SQLite table that has a text field with multiple carriage returns. I can't figure out how to represent a carriage return in a replace function.This is on a Windows system. I've tried Select Replace(field, '\n','') from table but it doesn't return the data unchanged at all. If I take the \n out of single quotes I just get an error. TIA Matt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replace extra carriage returns?
Select Replace(field, '\n','') from table but it doesn't return the data unchanged at all. If I take the \n out of single quotes I just get an error. What made you think that SQLite will understand C-style escape-sequences? It doesn't do that. If you're running this query from sqlite3 command line utility then try approach that Donald showed you (though maybe you'll need to add casts to text for both constants). If you're running this query from you application then things are a whole lot easier - just put into quotes real CR/LF codes using escaping that your language supports. Pavel On Thu, Sep 17, 2009 at 11:43 AM, Matt Williamson mattw1...@gmail.com wrote: I'm trying to clean out a SQLite table that has a text field with multiple carriage returns. I can't figure out how to represent a carriage return in a replace function.This is on a Windows system. I've tried Select Replace(field, '\n','') from table but it doesn't return the data unchanged at all. If I take the \n out of single quotes I just get an error. TIA Matt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] replace extra carriage returns?
How does the replace function identify a windows CRLF? I've tried using \n, \r, \p, 0D0A, etc. I just want to remove multiple carriage returns from a text typed field. Something like: Select replace (field1, '\n\n','\n') from table; I've just started dabbling with SQLite. I mainly work with MSSQL at the moment but I'm liking what I see so far. Also, is there a more comprehensive function list other than http://www.sqlite.org/lang_corefunc.html ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replace extra carriage returns?
Hi Matt, Regarding: Is there a more comprehensive function list other than http://www.sqlite.org/lang_corefunc.html; Is there an sqlite-supported function that's not listed there, or are you saying you want more functions? If the latter, sqlite struggles to keep the lite on, but you can define your own functions as desired. Regarding: I just want to remove multiple carriage returns from a text typed field. I think you'll want to use SELECT replace(field1, x'0d0a0d0a', x'0d0a'); Note that I'm assuming you already *have* the multiple cr/lf's in your text field. If you're importing data with the commandline utility, it allows very flexible definition of *field* separators, but I don't think you can redefine the *record* separator. So to eliminate empty lines on imported data, you could still do something like: -- Create a table with only one long field. -- Use something very unlikely as field separator CREATE TABLE raw(line); .separator '#$%' .import 'myRawFile.txt' raw -- delete any truly empty lines and output my clean data for later re-import. DELETE FROM RAW WHERE STRLEN(line) 1; .output 'myCleanFile.txt' SELECT * FROM raw; .output stdout OR -- you could use a tiny program in awk, sed, perl, etc. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replace extra carriage returns?
Also, is there a more comprehensive function list other than http://www.sqlite.org/lang_corefunc.html ? If you look closely at http://www.sqlite.org/lang.html you'll find these links: http://www.sqlite.org/lang_corefunc.html http://www.sqlite.org/lang_aggfunc.html http://www.sqlite.org/lang_datefunc.html And all these three links combined list all functions that SQLite understands unless you (or you database manager application) register some user-defined functions. Pavel On Thu, Sep 17, 2009 at 2:52 PM, Matt Williamson mattw1...@gmail.com wrote: How does the replace function identify a windows CRLF? I've tried using \n, \r, \p, 0D0A, etc. I just want to remove multiple carriage returns from a text typed field. Something like: Select replace (field1, '\n\n','\n') from table; I've just started dabbling with SQLite. I mainly work with MSSQL at the moment but I'm liking what I see so far. Also, is there a more comprehensive function list other than http://www.sqlite.org/lang_corefunc.html ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
