trim'ming a column

classic Classic list List threaded Threaded
9 messages Options
jic
Reply | Threaded
Open this post in threaded view
|

trim'ming a column

jic

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim'ming a column

Puneet Kishor-2
On Wed, Dec 23, 2009 at 4:08 PM, jose isaias cabrera
<[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim'ming a column

Griggs, Donald-3
In reply to this post by jic
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim'ming a column

Igor Tandetnik
In reply to this post by jic
jose isaias cabrera <[hidden email]>
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: trim'ming a column

jic
In reply to this post by Griggs, Donald-3

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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim'ming a column

jose isaias cabrera
In reply to this post by Puneet Kishor-2

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: trim'ming a column

jic
In reply to this post by Igor Tandetnik
Igor Tandetnik wrote...


> jose isaias cabrera <[hidden email]>
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim'ming a column

Griggs, Donald-3
In reply to this post by jic
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: trim'ming a column

jic

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users