wiljago wiljago - 18 days ago 6
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:

|c_name|
--------
|John Smith|
|Abe Lincoln|
|Taco Cat|


And the desired outcome is:

|c_name|
--------
|Smith|
|Lincoln|
|Cat|


Thanks very much for any help!

Answer

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, ' '))) ;