String To Table in SQL

Hello Friends,

Today, we are talking about another topic in SQL. Today, we will convert delimiter based string into table. The scenario is, we have a row and cell delimited string, where each cell value is separated by a delimiter (say $) and each row is again separated by a delimiter (say |). Next, this string may have some garbage value- means, such values, that we don't want to insert into SQL table. For example, we have a string "FirstName$LastName|". If LastName is blank or NULL, don't insert into table. Also, we need to add an identity column as well. Let's see how do we do it.

SQL Script:

DECLARE @Country NVARCHAR(MAX) = 'India'
DECLARE @Proposal1String NVARCHAR(MAX) = 'Sachin$Jain|Ram$Vinay|Lokesh$Kumar|Kulbhushan$|Rajeev$Sharma|Manoj$Kumar|'
DECLARE @DelimiterCell CHAR(1) = '$'
DECLARE @DelimiterRow CHAR(1) = '|'
DECLARE @ResultTable TABLE(SplittedRow NVARCHAR(MAX))

DECLARE @StartPosition INT, @EndPosition INT
SELECT @StartPosition = 1, @EndPosition = CHARINDEX(@DelimiterRow, @Proposal1String)
WHILE @StartPosition < LEN(@Proposal1String) + 1
BEGIN
	IF @EndPosition = 0 
		SET @EndPosition = LEN(@Proposal1String) + 1
		INSERT INTO @ResultTable(SplittedRow) 
		VALUES(SUBSTRING(@Proposal1String, @StartPosition, @EndPosition - @StartPosition))
		SET @StartPosition = @EndPosition + 1
		SET @EndPosition = CHARINDEX(@DelimiterRow, @Proposal1String, @StartPosition)
END

SELECT * FROM @ResultTable

DECLARE @FinalTable TABLE (SrNo INT IDENTITY(1,1), FName NVARCHAR(MAX), LName NVARCHAR(MAX), Country NVARCHAR(MAX))

INSERT INTO @FinalTable (FName, LName, Country) 
SELECT 
	SUBSTRING(SplittedRow, 0, CHARINDEX(@DelimiterCell, SplittedRow, 0)),
	SUBSTRING(SplittedRow, CHARINDEX(@DelimiterCell, SplittedRow, 0)+1, LEN(SplittedRow)),
	@Country
FROM @ResultTable

SELECT * FROM @FinalTable
DELETE FROM @FinalTable WHERE LName IS NULL OR LName = ''
SELECT * FROM @FinalTable

Output:

If you wish to do some activity upon the splitted table data and based upon that activity, you wish to either UPDATE the record in actual table or INSERT the new record then-

DECLARE @RowNumber INT = 1
DECLARE @MaxSrNo INT = (SELECT MAX(SrNo) FROM @FinalTable)
    
WHILE @RowNumber <= @MaxSrNo
BEGIN
	--DO YOUR ACTIVITY HERE
	--PRINT @RowNumber
	SET @RowNumber = @RowNumber + 1;
END

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !