I have a Column that has a . as a delimiter, it looks like so....
abc.efg.hij
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