split sql server table column holding values with pipe separator into two columns

maqskywalker
maqskywalker used Ask the Experts™
on
Hi experts,

I'm using SQL Server 20016.

I have a table  that looks like this. These are the grades for the fictitious Looney Toons Algebra 101 class.
The important column to look at is the Grade column.
That column holds the letter grade a pipe symbol as separator and then the score the student currently has in the class.

DesiredResult.PNG
This is the script to create the table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LooneyGradesAlgebra101](
	[StudentID] [int] NULL,
	[LastName] [nvarchar](40) NULL,
	[FirstName] [nvarchar](40) NULL,
	[Grade] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (1, N'Bunny', N'Bug', N'A+|100.00000')
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (2, N'Leghorn', N'Foghorn', N'A+|97.60321')
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (3, N'Duck', N'Daffy', N'D+|68.61600')
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (4, N'Pig', N'Porky', N'F|53.97300')
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (5, N'LePew', N'Pepe', N'B|85.02497')
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (6, N'Martian', N'Marvin', N'B|86.02591')
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (7, N'Gonzales', N'Speedy', N'B|84.02392')
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (8, N'Fudd', N'Elmer', N'A+|99.45')
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (9, N'Rodriguez', N'Slowpoke', N'B|84.02893')
INSERT [dbo].[LooneyGradesAlgebra101] ([StudentID], [LastName], [FirstName], [Grade]) VALUES (10, N'Sam', N'Yosemite', N'B|83.01192')

Open in new window


I would like to add two more columns to this query below.  I want to split the Grade column into two columns.
One of the new columns will be called Letter and hold the letter grade portion of the Grade column.
The other new column will be called Percent and hold the number portion of the Grade column.

SELECT [StudentID]
      ,[LastName]
      ,[FirstName]
      ,[Grade]
FROM [TestDatabase].[dbo].[LooneyGradesAlgebra101]

So my desired result from my query would look like this.

The two new columns are the ones in blue.

DesiredResult.PNG
How do I split the grade column into 2 columns in my query?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Test your restores, not your backups...
Top Expert 2016
Commented:
Try this:

SELECT [StudentID]
      ,[LastName]
      ,[FirstName]
      ,[Grade]
      ,SUBSTRING([Grade],1,CHARINDEX('|', [Grade])-1) [Letter]
      ,SUBSTRING([Grade],CHARINDEX('|', [Grade])+1, 99) [Percent]
FROM [LooneyGradesAlgebra101]

Open in new window


»bp

Author

Commented:
thanks. great solution.
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Welcome, glad that helped.


»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial