wiljago wiljago - 1 year ago 54
SQL Question

Update column to remove everything before and including a space in SQLite

I have looked at these somewhat related articles for ideas, and did a number of searches like "sqlite ltrim %" but I'm having a surprisingly hard time figuring out what I'm doing wrong.

What I want to do is remove everything before, and including a space in SQLite.

One major issue is that I have to use SQLite from PortableApps, and cannot use MySQL for this.

I tried:

UPDATE `temp_Week17` SET `c_name`=ltrim(%,' ');

I was thinking I could trim the space from the front easily enough after, but I get a "near '%': syntax error."

Just to see what would happen, I also tried:

UPDATE temp_Week17
SET c_name = LEFT(c_name, CHARINDEX(' ', c_name) - 1)
WHERE CHARINDEX(' ', c_name) > 0

And that produces a "near '(': syntax error."

Another one I tried was:

UPDATE temp_Week17 SET c_name = ltrim(c_name, ' ');

That one runs succesfully but does not change anything.

Updated to add per @scaisEdge:

The column is arranged as so:

|John Smith|
|Abe Lincoln|
|Taco Cat|

And the desired outcome is:


Thanks very much for any help!

Answer Source

You should concatenate the two string (the trimmed and the space)

  UPDATE temp_Week17 SET c_name = ltrim(c_name) || ' ' ;

or you need the space before the trimmed

  UPDATE temp_Week17 SET c_name = ' ' || ltrim(c_name)  ;

based on the sample provide seems you need substring

 UPDATE temp_Week17 SET c_name = ltrim(substr( c_name, instr(c_name, ' '))) ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download