MS SQL split data element based on delimiter-CodePudding

I have a database called Property with 1 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)