How to extract data from variable string and use in a select query

I have a question regarding how to extract data from string (variable length) and include it in the select queries.

For example, I have value in Portfolio_full_name as TS.PDO.CTS

(Portfolio_full_name = TS.PDO.CTS)

I would like to retrieve each word before the . and put it into another fields.

Portfolio_name = TS
Portfolio_category = PDO
Portfolio_subcategory = CTS

I am looking for to include this in the select statement before where condition (create开发者_StackOverflow CASE statement maybe?) Could you please let me know how could I do this?

In SQL Server (assuming that the format is fixed to NAME.CATEGORY.SUBCATEGORY and you've got the Portfolio_full_name column in some table, called atable here, and are updating the columns Portfolio_name, Portfolio_category and Portfolio_subcategory in the same table):

UPDATE atable
SET
  Portfolio_name = SUBSTRING(s.Portfolio_full_name, 1, DotPos1 - 1),
  Portfolio_category = SUBSTRING(s.Portfolio_full_name, DotPos1 + 1, DotPos2 - DotPos1 - 1),
  Portfolio_subcategory = SUBSTRING(s.Portfolio_full_name, DotPos2 + 1, FullLen - DotPos2)
FROM (
  SELECT
    Portfolio_full_name,
    DotPos1 = CHARINDEX('.', Portfolio_full_name),
    DotPos2 = CHARINDEX('.', Portfolio_full_name, CHARINDEX('.', Portfolio_full_name) + 1),
    FullLen = LEN(Portfolio_full_name)
  FROM (
    SELECT Portfolio_full_name FROM atable
  ) s
) s
WHERE atable.Portfolio_full_name = s.Portfolio_full_name