Here is SQL Server:
declare @Input NVARCHAR(MAX)
set @Input = 'BENEACCTNAME:Juan Dela Cruz; BENEACCT:0123456; BENEBNKNAME:Dummy Bank; BENEBNKADD:123 32nd Ave, Earth; BENEBNKMET:BCD; BENEBNKCODE:1111133333; BENEBNKCC:AR; INTBNKNAME:; INTBNKADD:; INTBNKMET:; INTBNKCODE:; PAYDET1:DetailsAcc; PAYDET2:; PAYDET3:; PAYDET4:; BNKTOBNKINFO1:; BNKTOBNKINFO2:; BNKTOBNKINFO3:; BNKTOBNKINFO4:; BNKTOBNKINFO5:; BNKTOBNKINFO6:; BENEBNKADD1:; BENEBNKADD2:; BENEBNKADD3:;'
DECLARE @Output TABLE (
name NVARCHAR(100),
value NVARCHAR(100)
)
DECLARE @StartIndex INT, @EndIndex INT, @name NVARCHAR(100), @value NVARCHAR(100), @Character CHAR(1)
SET @Character = ';'
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
IF(@EndIndex > 1)
BEGIN
declare @index int
set @index = CHARINDEX(':', @Input)
SET @name = SUBSTRING(@Input, @StartIndex, @index - 1)
SET @value = SUBSTRING(@Input, @index + 1, @EndIndex - @index - 1)
INSERT INTO @Output(name, value) VALUES (@name, @value)
END
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
select * from @Output
Regards,
Saad