I have a Column that has a . as a delimiter, it looks like so....
I want a query that turns this into three columns, Col1, Col2, and Col3. I am wondering what the fastest way to do this is. So far I haven't been able to do very well with my limited database experience. I've got a function:
CREATE FUNCTION [dbo].[split]( @delimited NVARCHAR(MAX), @delimiter NVARCHAR(100) ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)) AS BEGIN DECLARE @xml XML SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>' INSERT INTO @t(val) SELECT r.value('.','varchar(MAX)') as item FROM @xml.nodes('/t') as records(r) RETURN END
This is how I'm doing it right now but I believe it could be made to go much faster, I'm also open to a significantly better function or outside the box ideas for splitting strings.I believe I'm running this
dbo.split(Name, '.') three times and could only be running it once.
SELECT Col1 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '1'), Col2 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '2'), Col3 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '3') FROM Mains
any help would be greatly appreciated