0

I am Using SQL Server 2008. i have a Table of "Course", having one column with name of "Courses" . I insert "Courses" in table "Course" and get a string For example "Math English Physics".

How do I split a string so I can access each course? How can I split the string by a space and access the item at index 1 which should return "English"?

AHMAD SUMRAIZ
  • 535
  • 8
  • 21
  • get the result first and split next – Linga Jan 16 '14 at 08:16
  • 2
    Is this a legacy system? Can you instead normalize your database? Apparently you have a 1:n relationship between something and course, if possible you should design the db accordingly. – Petko Petkov Jan 16 '14 at 08:17

1 Answers1

1

Create a User Defined Function (UDF) as below:

 Declare @course varchar(200) = 'Math English Physics'
Declare @individual varchar(20) = null

WHILE LEN(@course) > 0
BEGIN
    IF PATINDEX('% %',@course) > 0
    BEGIN
        SET @individual = SUBSTRING(@course, 0, PATINDEX('% %',@course))
        SELECT @individual

        SET @course = SUBSTRING(@course, LEN(@individual + ' ') + 1,
                                                     LEN(@course))
    END
    ELSE
    BEGIN
        SET @individual = @course
        SET @course = NULL
        SELECT @individual
    END
END

i hope this may solve your problem.

BUt i believe you should restructure your database and normalize it.

Pawan
  • 1,065
  • 5
  • 10