This is still indicative of a serious schema problem. Hopefully you intend to use the solution to resolve that issue?

Assuming you have the ID column, and it's always true that the rows that do not start with 'Category-' are products that belong to the preceding category:

DECLARE @Table TABLE (Name VARCHAR(50), ID INT);
INSERT INTO @Table (Name, ID) VALUES
('Category-Phones',1), ('Apple iPhone 15 Pro Max 256GB',2), ('Samsung Galaxy S23 Ultra 256GB',3), ('Motorola Razr+ 256GB',4),
('Category-Wearables',5), ('Google Pixel Watch',6), ('Fitbit Sense 2',7), ('Garmin Venu 3 with Silicone B.',8),
('Category-Phone Accessories',9), ('Apple MagSafe Battery Pack',10), ('Tile Mate Essential (2022) 4P.',11), ('Apple Clear Case iPhone 11Pro ',12);

;WITH Categories AS (
SELECT ID AS CategoryID, LEAD(ID,1) OVER (ORDER BY (SELECT ID)) AS NextCategory, Name AS CategoryName
  FROM @Table t
 WHERE Name LIKE 'Category-%'
)

SELECT CategoryID, p.ID AS ProductID, c.CategoryName, p.Name AS ProductName
  FROM Categories c
    LEFT OUTER JOIN @Table p
      ON p.ID > c.CategoryID
      AND (p.ID < c.NextCategory OR c.NextCategory IS NULL);

So here we find the category rows (1, 5 and 9) (in the CTE ) and then join back to the table to get all rows that are greater than the category ID and less than the NEXT category ID.

This will only work when the conditions are true, and is BEGGING to have a proper relationship defined for it. Hopefully you are just trying to sanitize the data to achieve that.
| CategoryID | ProductID | CategoryName | ProductName |
| ------------ | ------------ | ------------ | ------------ |
| 1 | 2 | Category-Phones | Apple iPhone 15 Pro Max 256GB |
| 1 | 3 | Category-Phones | Samsung Galaxy S23 Ultra 256GB |
| 1 | 4 | Category-Phones | Motorola Razr+ 256GB |
| 5 | 6 | Category-Wearables | Google Pixel Watch |
| 5 | 7 | Category-Wearables | Fitbit Sense 2 |
| 5 | 8 | Category-Wearables | Garmin Venu 3 with Silicone B. |
| 9 | 10 | Category-Phone Accessories | Apple MagSafe Battery Pack |
| 9 | 11 | Category-Phone Accessories | Tile Mate Essential (2022) 4P. |
| 9 | 12 | Category-Phone Accessories | Apple Clear Case iPhone 11Pro |

First, data in a SQL Server table has no implied order, so you need to unambiguously define one. I have added an IDENTITY value to your data for this purpose.

From there, selecting the products is straight forward using a LIKE operator. As for choosing the associated Category, you can use the OUTER APPLY (SELECT TOP 1 ... ORDER BY ...) pattern to select the best matching row - in this case the closest preceding "Category" row.

SELECT P.Name AS ProductName, C.CategoryName
FROM Products P
OUTER APPLY (
    SELECT TOP 1 C.Name AS CategoryName
    FROM Products C
    WHERE C.Id < P.Id
    AND C.Name LIKE 'Category%'
    ORDER BY C.Id DESC
) C
WHERE P.Name LIKE 'Product%'
ORDER BY Id

If a product appears before the first category, the above would return a null CategoryName.

Results:
| ProductName | CategoryName |
| ------------ | ------------ |
| Product1 | Category1 |
| product2 | Category1 |
| Product3 | Category1 |
| Product4 | Category2 |
| Product5 | Category2 |
| Product6 | Category2 |
| Product7 | Category3 |
| Product8 | Category3 |
| Product9 | Category3 |

See this db<>query