2

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!

Community
  • 1
  • 1
wiljago
  • 147
  • 3
  • 14

1 Answers1

2

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, ' '))) ;
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thanks again for your help, @scaisEdge! The two code snippets say they run successfully, but don't actually change the column at all. Any ideas why that could be happening? – wiljago Nov 20 '16 at 19:38
  • then try update you answer and show a proper data sample and the expected result.. – ScaisEdge Nov 20 '16 at 19:50
  • Done. Thanks for the tip. – wiljago Nov 20 '16 at 19:58
  • you want remove the first string and leave only the second? – ScaisEdge Nov 20 '16 at 20:01
  • More or less. It's all one string including spaces in the column, and some of the last names include spaces. I want to preserve everything that falls after the first occurrence of a space character. – wiljago Nov 20 '16 at 20:06
  • Thank you. When I entered: UPDATE temp_Week17 SET c_name = ltrim(substr(instr(c_name, ' '))) ; it returned "wrong number of arguments to function substr(): UPDATE temp_Week17 SET c_name = ltrim(substr(instr(c_name, ' '))) ;" – wiljago Nov 20 '16 at 20:07
  • Actually I got it to do something by adding another value at the end like so: UPDATE temp_Week17 SET c_name = ltrim(substr(instr(c_name, ' '),0)) ; (It doesn't matter what 0 is, I tried %, '%', 5, and 10, also) When I run it that way I don't get the wrong number of arguments error, but it does change the content of c_name into a number. – wiljago Nov 20 '16 at 20:25