Is there any command to execute SQL and cut a last words in row of database?

Question

I'm importing a database to DB Browser SQLite and need to execute SQL and cut a last words include '-' as in red circle for all rows under 'slug' in database.

Is there any command to do these?

Thanks

Answer 1

Is there any command to execute SQL and cut a last words in row of database?

In Short NO.

However, you could extract the last word, but it would be cumbersome assuming there are no further specific regard word, such as it's length.

It would probably be far more productive to design the database with it's intended use taken into consideration.

For example the following would extract the last word with the following assumptions/conditions/constraints :-

• The last word is at least 1 character in length and less than 30 characters in length.
• There is just the one separator character, namely -.

:-

SELECT *,
    CASE 
        WHEN instr(substr(slug,length(slug) -2),'-') > 0
            THEN substr(slug,length(slug)-1)
        WHEN instr(substr(slug,length(slug) -3),'-') > 0
            THEN substr(slug,length(slug)-3)
        WHEN instr(substr(slug,length(slug) -4),'-') > 0
            THEN substr(slug,length(slug)-4)
        WHEN instr(substr(slug,length(slug) -5),'-') > 0
            THEN substr(slug,length(slug)-4)
        WHEN instr(substr(slug,length(slug) -6),'-') > 0
            THEN substr(slug,length(slug)-5)
        WHEN instr(substr(slug,length(slug) -7),'-') > 0
            THEN substr(slug,length(slug)-6)
        WHEN instr(substr(slug,length(slug) -8),'-') > 0
            THEN substr(slug,length(slug)-7)
        WHEN instr(substr(slug,length(slug) -9),'-') > 0
            THEN substr(slug,length(slug)-8)
        WHEN instr(substr(slug,length(slug) -10),'-') > 0
            THEN substr(slug,length(slug)-9)
        WHEN instr(substr(slug,length(slug) -11),'-') > 0
            THEN substr(slug,length(slug)-10)
        WHEN instr(substr(slug,length(slug) -12),'-') > 0
            THEN substr(slug,length(slug)-11)
        WHEN instr(substr(slug,length(slug) -13),'-') > 0
            THEN substr(slug,length(slug)-12)
        WHEN instr(substr(slug,length(slug) -14),'-') > 0
            THEN substr(slug,length(slug)-13)
        WHEN instr(substr(slug,length(slug) -15),'-') > 0
            THEN substr(slug,length(slug)-14)
        WHEN instr(substr(slug,length(slug) -16),'-') > 0
            THEN substr(slug,length(slug)-15)
        WHEN instr(substr(slug,length(slug) -17),'-') > 0
            THEN substr(slug,length(slug)-16)
        WHEN instr(substr(slug,length(slug) -18),'-') > 0
            THEN substr(slug,length(slug)-17)
        WHEN instr(substr(slug,length(slug) -19),'-') > 0
            THEN substr(slug,length(slug)-18)
        WHEN instr(substr(slug,length(slug) -20),'-') > 0
            THEN substr(slug,length(slug)-19)
        WHEN instr(substr(slug,length(slug) -21),'-') > 0
            THEN substr(slug,length(slug)-20)
        WHEN instr(substr(slug,length(slug) -22),'-') > 0
            THEN substr(slug,length(slug)-21)
        WHEN instr(substr(slug,length(slug) -23),'-') > 0
            THEN substr(slug,length(slug)-22)
        WHEN instr(substr(slug,length(slug) -24),'-') > 0
            THEN substr(slug,length(slug)-23)
        WHEN instr(substr(slug,length(slug) -25),'-') > 0
            THEN substr(slug,length(slug)-24)
        WHEN instr(substr(slug,length(slug) -26),'-') > 0
            THEN substr(slug,length(slug)-25)
        WHEN instr(substr(slug,length(slug) -27),'-') > 0
            THEN substr(slug,length(slug)-26)
        WHEN instr(substr(slug,length(slug) -28),'-') > 0
            THEN substr(slug,length(slug)-27)
        WHEN instr(substr(slug,length(slug) -29),'-') > 0
            THEN substr(slug,length(slug)-28)
        WHEN instr(substr(slug,length(slug) -30),'-') > 0
            THEN substr(slug,length(slug)-29)
        ELSE 'ooops not found'
    END AS lastword
FROM mytable;

• The above could be shorter accordingly if the minimum length of the last word were greater than 1 and/or the maximum length is less than 30
• the above will output all columns of the table plus a new one named lastword

For example if the input table were (data is different as I wasn't going to try copying from a screenshot, nor try to count to see if the last words were the same length):-

The result from the above would be :-

Answer 2

The ending strings all look like they are the same length. If so, you can do:

select substr(slug, 1, length(slug) - <n>)

where <n> is the length of the string to be removed.

Answer 3

select SUBSTRING_INDEX(slug,"-", -1) from xxx.