SQL - Generate a CTE by using every 3 characters from a value as rows : data example: '255002001255255001255001004002255007002'

There is a formula that every 3 characters belongs to a group, and within those 3 characters, the number belongs to a id. For example, the first 3 characters '255' belong to group 1 and 255 is related to an id in another table.

I need to store every 3 characters in the value above that comes from a table. I want to use a CTE that stores this information so I can reference it in a query to generate a report. Its a third party database so I only have read access.

I thought of using SUBSTRING(tablevalue, 1,3), but dont know how to keep doing it for the entire value

*First post, still learning how to phrase my question