In a newer version of SQL Server you can use
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[
^].
For older version of SQL Server, you need to create custom function (for example:
Converting comma separated data in a column to rows for selection[
^] ) or write query, which uses
CTE[
^].
See:
CREATE TABLE StupidData
(
LongString NVARCHAR(MAX)
)
INSERT INTO StupidData (LongString)
VALUES(N'aaaaaaaaaaaaaa.bbbbbbbbbbbbb.cccccccccccccc.ddddddddddddddd');
;WITH CTE AS
(
SELECT 1 AS Level, LEFT(LongString, CHARINDEX('.', LongString)-1) AS WordPart,
RIGHT(LongString, LEN(LongString)- CHARINDEX('.', LongString)) AS Remainder
FROM StupidData
WHERE CHARINDEX('.', LongString)>0
UNION ALL
SELECT Level + 1 AS Level, LEFT(Remainder, CHARINDEX('.', Remainder)-1) AS WordPart,
RIGHT(Remainder, LEN(Remainder)- CHARINDEX('.', Remainder)) AS Remainder
FROM CTE
WHERE CHARINDEX('.', Remainder)>0
UNION ALL
SELECT Level + 1 AS Level, Remainder AS WordPart, NULL AS Remainder
FROM CTE
WHERE CHARINDEX('.', Remainder)=0
)
SELECT *
FROM CTE
Returns:
Level WordPart Remainder
1 aaaaaaaaaaaaaa bbbbbbbbbbbbb.cccccccccccccc.ddddddddddddddd
2 bbbbbbbbbbbbb cccccccccccccc.ddddddddddddddd
3 cccccccccccccc ddddddddddddddd
4 ddddddddddddddd
Online example:
SQL Server 2019 | db<>fiddle[
^]