There is a built in function. STRING_SPLIT. It requires compatibility level 130.

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16

That will take care of you.

Try this to see what it does:

select value from STRING_SPLIT ('3,7,12,33,43',',')

so like:

select * from myTable T
where 3 in (select value from string_split(T.categoryIds,','))