Remove all after certain character/phrase SQLITE

Question

Table in SQLITE, want to simple way to delete everything to a right of a set phrase/character in the Company_name_ column, in this case everything after "LLC":

Company_name_
Example LLC $42
Example llc,klp
Example LLc jim

becomes

Company_name_
Example LLC
Example llc
Example LLc

Tried Set Charindex and Substr but getting syntax errors. Thank you

Answer 1

You can do it with string functions SUBSTR() and INSTR().

If you want a SELECT query use a CASE expression with the operator LIKE to check if the column value contains 'LLC' or not:

SELECT CASE
         WHEN Company_name_ LIKE '%LLC%'
           THEN SUBSTR(
                  Company_name_,
                  1,
                  INSTR(UPPER(Company_name_), 'LLC') + LENGTH('LLC') - 1
                ) 
         ELSE Company_name_ 
       END Company_name_   
FROM tablename;

If you want to update the table:

UPDATE tablename
SET Company_name_ = SUBSTR(
                      Company_name_,
                      1,
                      INSTR(UPPER(Company_name_), 'LLC') + LENGTH('LLC') - 1
                    ) 
WHERE Company_name_ LIKE '%LLC%';

See the demo.