Unfortunately, the simple charindex approach is only extracting the first value on each line, the second and further values are being discarded.

In order to use "distinct" the tilde-delimited values must first be brought out into separate rows.
The UDF tilde_extras() shown below does that, on the fly returning a temp table containing all the values.
It caters for any number of values, e.g. one~three~six~ten

>> I want to keep multiple "categories" in this fashion using a tilde.

If you've got control over the design, I wouldn't recommend storing multiple values that way. As you can already see it's difficult to work with. A normalized table (one value per row) is the way to go imo.

_agx_: I currently use only one table... it has category, question and ID fields. The category field is long enough to contain tilde separated categories should a question fall into multiple categories.

Are you suggesting a table for categories and then another for questions? How, in that case, would you assign multiple categories to a question?

>>Are you suggesting a table for categories and then another for questions? How, in that case, would you assign multiple categories to a question?<<
Most definitely.
You need three tables: Questions, Categories and QuestionCategoryXRef.
Categories is just a simple table of all your categories
You will remove the category from Questions and add it to QuestionCategoryXRef.

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.