Question

For sqlite: Write a single SQL statement to list all the tracks that do not have the exact word 'time' (including both upper and lower cases) as part of the name in the Track table, but they have 'time' being part of a word in the name. For instance, it should include track names with words like 'wartime' or 'times' in the output I have to find a way to limit the exact word but the word should be part of a word. For example, 'Time' is not okay, but 'Sometimes' is acceptable.

Answer 1

You can achieve this using the UPPER() function to exclude exact word Time in insensitive cases and LIKE to include words having time as a part of it.

• SELECT name
• FROM Track
• WHERE UPPER(name) <> 'TIME' and UPPER(name) LIKE UPPER('time%') ;

Note that this will include all cases of subpart Time in the word, eg SomeTime, sometimes, someTiMEs all will be accepted. If you want to include only 'time' as a subpart then the second condition in the where clause will have no UPPER() just use name like 'time%'

i.e,

• WHERE UPPER(name) <> 'TIME' and name LIKE 'time%'