My favorite method is to use
Common Table Expressions[
^].
Example:
DECLARE @fullAddress NVARCHAR(255)
SET @fullAddress = '140/3 abcd, narayana, delhi,pin code-201101'
;With AddressParts AS
(
SELECT 1 AS PartNumber, LTRIM(LEFT(@fullAddress, CHARINDEX(',',@fullAddress)-1)) AS Part, RIGHT(@fullAddress, LEN(@fullAddress) - CHARINDEX(',',@fullAddress)) AS Remainder
UNION ALL
SELECT PartNumber + 1 AS PartNumber, LTRIM(LEFT(Remainder, CHARINDEX(',',Remainder)-1)) AS Part, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',',Remainder)) AS Remainder
FROM AddressParts
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT PartNumber + 1 AS PartNumber, LTRIM(Remainder) AS Part, NULL AS Remainder
FROM AddressParts
WHERE CHARINDEX(',', Remainder)=0
)
SELECT *
FROM AddressParts
Above query returns:
PartNo. Part Remainder
1 140/3 abcd narayana, delhi,pin code-201101
2 narayana delhi,pin code-201101
3 delhi pin code-201101
4 pin code-201101 NULL