CODEWITHSUNDEEP
sqlsqlite
wiljago
wiljago

Reputation: 146

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!

Upvotes: 2

Views: 1352

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 3

Related Questions