I have an sqlite table with a column of codes separated by '.' or '-' or both. For example:

How do I create a new column taking the first part of the code only? Preferably as part of an sql command that can handle nonetypes.

I can get a column in python to insert using the following code:

def get_column(src, table, column):
    col = src.execute('SELECT %s FROM %s' % (column, table)).fetchall()
    col = ['None' if v is None else v for v in col] # replace nonetypes with string
    col = list(set(col))
    col = [x.split('-', 1)[0] for x in col]

return col

but is there a way to do it in sql directly?

CodePudding user response：

If you want a generic solution with SQL code for any number of chars:

WITH cte(char) AS (VALUES ('.'), ('-')) -- you can add more chars in the list
SELECT code,
       SUBSTR(t.code, 1, COALESCE(MIN(INSTR(t.code, c.char)) - 1, LENGTH(t.code))) new_code
FROM tablename t LEFT JOIN cte c
ON INSTR(t.code, c.char)
GROUP BY t.code;

See the demo.