SQL Server : split data element based on delimiter-CodePudding

I have a database called Property with a table called Location. The data looks like this:

RecordID     Location
-----------------------
    1        1/21/s15
    2        8/1/21c59
    3        1//
    4        9//72

I have a script that reads records from the table and inserts them into a second table called ExpandedLocation.

This is the code of my script:

INSERT INTO [Property].[dbo].[ExpandedLocation] (LocationA, LocationB, LocationC)
    SELECT 
        dbo.fnBuildABC(Location, 1), 
        dbo.fnBuildABC(Location, 2), 
        dbo.fnBuildABC(Location, 3)
    FROM 
        [Property].[dbo].[Location]

This code should call the function fnBuildABC and pass it 2 parameters, Location and a number. The function should take in the parameters and split the first parameter on the slash and return either the 1st, 2nd, or 3rd portion of the passed string.

So, for example, on the first read of the Location table, I pick up the value 1/21/s15.

The function should return the following:

Parameter Value    Returned Value
---------------------------------
Location, 1            1
Location, 2            21
Location, 3            s15

On the second read of the Location table, I pick up the value 8/1/21c59. The function should return the following:

Parameter Value      Returned Value
-----------------------------------
Location, 1             8
Location, 2             1
Location, 3             21c59

I'm at a loss as to how to split the passed string in the function without actually inspecting each character of the string one at a time.

Any suggestions on how to start this process would be greatly appreciated. Thank you.

CodePudding user response：

MSSQL has a built in

CodePudding user response：

create or alter function BuildABC(@location varchar (10)

CodePudding user response：

You may try using XML method as the following:

CREATE FUNCTION fnBuildABC( @loc VARCHAR(MAX))
RETURNS @splited TABLE
(
  [Parameter Value] INT,
  [Returned Value] VARCHAR(50)
)
AS
BEGIN
DECLARE @xml xml;
SET @xml = N'<root><p>'   replace(@loc, '/','</p><p>')   '</p></root>';

INSERT INTO @splited
  SELECT ROW_NUMBER() OVER (ORDER BY l_pos) AS pos,
  l_pos.value('.', 'VARCHAR(50)') AS val
  FROM
  @xml.nodes('//root/p') AS [Items](l_pos)

RETURN;
END;

To get all portions of a location call the function as the following:

SELECT CONCAT('Location: ', [Parameter Value]) AS [Parameter Value], [Returned Value]
FROM
(
  SELECT L.RecordID, P.* FROM ExpandedLocation L
  OUTER APPLY
  fnBuildABC(Location) P
) T
WHERE RecordID = 1;

To get specific portions, i.e. 1 & 2:

SELECT CONCAT('Location: ', [Parameter Value]) AS [Parameter Value], [Returned Value]
FROM
(
  SELECT L.RecordID, P.* FROM ExpandedLocation L
  OUTER APPLY
  fnBuildABC(Location) P
) T
WHERE RecordID = 1 AND [Parameter Value] IN (1,2);

See demo.