i have sqlite table cars that contains some cars data and a urls from different websites in "source" column what i want to do is clean the urls in "SOURCE" column that are for "example1" from : https://www.example1.com/car-details/202205316358623?some_text_here&here to : https://www.example1.com/car-details/202205316358623 but only for "example1" site thank you

CodePudding user response：

With sqlite you're fairly limited, best I can come up with is something like this:

If ? is in SOURCE, then take SOURCE from its starting character to where ? is located. Otherwise, just return SOURCE.

substr(SOURCE, 1, iff(instr(SOURCE, '?') > 0, instr(SOURCE, '?'), NULL))

You might have to replace the second instr(SOURCE, '?') with instr(SOURCE, '?') - 1.