We help IT Professionals succeed at work.

Split function that allows multiple character delimiter

Hi I have a table with a varchar column that is delimited by char(227)+char(228)
ie,
OrNo=7807ãäSeqNo=1ãäPStopType=PãäPCity=TillsonburgãäPSt=ONãäPZIP=N4G4J1ãäPAdr1=10 ROUSE STREETãäPAdr2=ãäPLat=42.8625ãäPLon=-80.7267ãäDStopType=DãäDCity=GaffneyãäDSt=SCãäDZIP=29341ãäDAdr1=121 PAN AMERICAN DRIVE

would anyone be able to point me to a split function that accepts a multiple character value as a delimter?

Thank you!
Comment
Watch Question

Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Are you looking for a purely SQL solution, or are you planning on handling the data with a server-side script such as PHP?
Freelance IT Consultant
Commented:
A sample split function in SQL if that is what you are asking for:

CREATE FUNCTION [dbo].[ufn_SplitString](@source NVARCHAR(MAX), @delimiter NVARCHAR(10))
RETURNS @tblSplittedValues TABLE(Item NVARCHAR(200))
AS
BEGIN
	DECLARE @item NVARCHAR(200) = NULL

	WHILE LEN(@source) > 0
	BEGIN
		IF CHARINDEX(@delimiter, @source) > 0
		BEGIN
			SET @item = SUBSTRING(@source, 0, CHARINDEX(@delimiter,@source))
        
			INSERT @tblSplittedValues VALUES(@item)

			SET @source = SUBSTRING(@source, LEN(@item + @delimiter) + 1, LEN(@source))
		END
		ELSE
		BEGIN
			INSERT @tblSplittedValues VALUES(@source)
			SET @source = NULL
		END
	END

	-- End function
	RETURN
END

Open in new window

Author

Commented:
Hi Chris, an sql solution would be preferable; however, I would also be able to implement a c# CLR function ...
Duy, I tried your function, it doesn't seem to be splitting for me ...
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
How do you want to split it?
In many columns?
Or you just want to get rid of those characters and let the value in the same field?

Author

Commented:
Hi Duy, I've gone back and re tried your solution as:


declare @delim as varchar(max) = CHAR(227) + CHAR(228)
;with cte as
(
select colSplit from csvTable
)
select * from
cte as a
cross apply
dbo.ufn_SplitString(a.colSplit,@delim) as b

and it did end up working, please disregard my original reply, the problem was with how I was applying it.