how to split the column into multiple columns using delimeter in SQL server?-CodePudding

I have below column in my table

I would like to split the column using decimeter '-' and create a new column in SQL Server.

Required output

I used parsename in the query but no success.

select 
ID,
PARSENAME(ID,'-', 1) AS EmployeeID from timeus;

I checked other posts but not able to solve it.

Can anyone advise how to to split in SQL server?

CodePudding user response：

You can use parsename like you have tried, like so:

select ID, ParseName(Replace(ID, '-', '.'), 3) AS EmployeeID 
from timeus;

CodePudding user response：

You can use this script to split the data:

CREATE TABLE dbo.NotProvided
(
    Category NVARCHAR(50)
);
GO

INSERT INTO dbo.NotProvided
(
    Category
)
VALUES
('103-Local IT-HHH'),
('102-HDHD-2737'),
('104-HHFY-XXX');


SELECT *,
       LEFT(Category, CHARINDEX('-', Category) - 1) as Id
FROM dbo.NotProvided;

CodePudding user response：

You can use substring function in MSSQL server.

    Select Category,SUBSTRING(Category,1,3) As Employeeid From NotProvided;