Re: [sqlite] trim'ming a column
P Kishor wrote... > On Wed, Dec 23, 2009 at 4:08 PM, jose isaias cabrera wrote: >> >> Greetings and salutations. >> >> I would like to trim a column from all white spaces. I know how to do it >> programmatically, but I would like to do it right to the DB. Is this a >> possibility? >> >> Image this statement: >> >> CREATE TABLE LSOpenJobs >> ( >> id integer primary key, >> ProjID integer, >> subProjID, >> parent, >> vEmail, >> notes, >> status >> ); >> >> say that I want o clean up the vEmail column by trimming all whitespaces >> at >> the beginning and the end of the data. I know that trim(vEmail) will do >> it, >> but what would be the command to run to trim all of the existing records? >> > > SELECT trim(col_name) FROM table > > If you want to create a new table, just SELECT the desired columns > into a new table > > CREATE TABLE trimmed_table AS SELECT trim(col1), trim(col2)... FROM > old_table; > >> Also, how to I trim specific characters? Say tab, or char(0) or char(X), >> etc? I know that I do trim(vEmail,Y), but how do I represent tab? or >> newline? etc.? >> > > \t for tab, \n for newline and so on. > > > > >> thanks, >> >> josé >> > Thanks Puneet. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim'ming a column
Griggs, Donald wrote... >> UPDATE LSOpenJobs >> SET vEmail = TRIM(vEmail); >> >> Given the state of the world economy, I hope that LSOpenJobs is an >> ever-expanding table. ;-) > > Why do you say this? Will the table grow because of this statement? I > have fixed the client data entry to take care of this in the future, but > there are a lot of records that I didnot clean previously, so I need to do > this, at least once. > > = > Jose, > > I beg your pardon. My comment ending with ;-) was simply a poor joke, > presuming that LSopenJobs was a list of available jobs -- something I > hoped would increase during a time of large unemployment in the world. Ha ha ha... very good. I get it now... :-) That is comedy, right there. I don't care what anybody says... Heck, it's so funny that I am going to laugh in Spanish, Ja ja ja ja ja... > The sql below should not increase the size of your database. > UPDATE LSOpenJobs >SET vEmail = TRIM(vEmail); thanks. > > Wishing you peace and prosperity, thanks and likewise. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim'ming a column
> UPDATE LSOpenJobs > SET vEmail = TRIM(vEmail); > > Given the state of the world economy, I hope that LSOpenJobs is an > ever-expanding table. ;-) Why do you say this? Will the table grow because of this statement? I have fixed the client data entry to take care of this in the future, but there are a lot of records that I didnot clean previously, so I need to do this, at least once. = Jose, I beg your pardon. My comment ending with ;-) was simply a poor joke, presuming that LSopenJobs was a list of available jobs -- something I hoped would increase during a time of large unemployment in the world. The sql below should not increase the size of your database. UPDATE LSOpenJobs SET vEmail = TRIM(vEmail); Wishing you peace and prosperity, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim'ming a column
Igor Tandetnik wrote... > jose isaias cabrera > wrote: >> I would like to trim a column from all white spaces. I know how to >> do it programmatically, but I would like to do it right to the DB. Is >> this a possibility? > > update mytable set mycolumn=trim(mycolumn); > >> Also, how to I trim specific characters? Say tab, or char(0) or >> char(X), etc? I know that I do trim(vEmail,Y), but how do I >> represent tab? or newline? etc.? > > If you do it in your program, just put those characters in the query > string or bound parameter string, using facilities of your programming > language. E.g. in C that would be something like "trim(vEmail, ' \t\n')". > > If you do it manually from, say, command line interface, you can do this: > > update mytable set mycolumn=trim(mycolumn, cast(X'20090A' as text)); > > 20 being the ASCII code of space, 09 of tab and 0A of line feed. > thanks Igor. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim'ming a column
Griggs, Donald wrote... > Regarding: I know that trim(vEmail) will do it, but what would be the > command to run to trim all of the existing records? > > UPDATE LSOpenJobs > SET vEmail = TRIM(vEmail); > > Given the state of the world economy, I hope that LSOpenJobs is an > ever-expanding table. ;-) Why do you say this? Will the table grow because of this statement? I have fixed the client data entry to take care of this in the future, but there are a lot of records that I didnot clean previously, so I need to do this, at least once. > > Regarding: Also, how to I trim specific characters? > > If you don't need to keep such characters at ALL, you can use REPLACE with > hex literals to change unwanted, non-whitespace characters to spaces, then > TRIM. Thanks. > > ___ > 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
Re: [sqlite] trim'ming a column
jose isaias cabrera wrote: > I would like to trim a column from all white spaces. I know how to > do it programmatically, but I would like to do it right to the DB. Is > this a possibility? update mytable set mycolumn=trim(mycolumn); > Also, how to I trim specific characters? Say tab, or char(0) or > char(X), etc? I know that I do trim(vEmail,Y), but how do I > represent tab? or newline? etc.? If you do it in your program, just put those characters in the query string or bound parameter string, using facilities of your programming language. E.g. in C that would be something like "trim(vEmail, ' \t\n')". If you do it manually from, say, command line interface, you can do this: update mytable set mycolumn=trim(mycolumn, cast(X'20090A' as text)); 20 being the ASCII code of space, 09 of tab and 0A of line feed. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim'ming a column
Regarding: I know that trim(vEmail) will do it, but what would be the command to run to trim all of the existing records? UPDATE LSOpenJobs SET vEmail = TRIM(vEmail); Given the state of the world economy, I hope that LSOpenJobs is an ever-expanding table. ;-) Regarding: Also, how to I trim specific characters? If you don't need to keep such characters at ALL, you can use REPLACE with hex literals to change unwanted, non-whitespace characters to spaces, then TRIM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim'ming a column
On Wed, Dec 23, 2009 at 4:08 PM, jose isaias cabrera wrote: > > Greetings and salutations. > > I would like to trim a column from all white spaces. I know how to do it > programmatically, but I would like to do it right to the DB. Is this a > possibility? > > Image this statement: > > CREATE TABLE LSOpenJobs > ( > id integer primary key, > ProjID integer, > subProjID, > parent, > vEmail, > notes, > status > ); > > say that I want o clean up the vEmail column by trimming all whitespaces at > the beginning and the end of the data. I know that trim(vEmail) will do it, > but what would be the command to run to trim all of the existing records? > SELECT trim(col_name) FROM table If you want to create a new table, just SELECT the desired columns into a new table CREATE TABLE trimmed_table AS SELECT trim(col1), trim(col2)... FROM old_table; > Also, how to I trim specific characters? Say tab, or char(0) or char(X), > etc? I know that I do trim(vEmail,Y), but how do I represent tab? or > newline? etc.? > \t for tab, \n for newline and so on. > thanks, > > josé > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, Wisconsin, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] trim'ming a column
Greetings and salutations. I would like to trim a column from all white spaces. I know how to do it programmatically, but I would like to do it right to the DB. Is this a possibility? Image this statement: CREATE TABLE LSOpenJobs ( id integer primary key, ProjID integer, subProjID, parent, vEmail, notes, status ); say that I want o clean up the vEmail column by trimming all whitespaces at the beginning and the end of the data. I know that trim(vEmail) will do it, but what would be the command to run to trim all of the existing records? Also, how to I trim specific characters? Say tab, or char(0) or char(X), etc? I know that I do trim(vEmail,Y), but how do I represent tab? or newline? etc.? thanks, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
