How to split long string piped delimited text in SQL 2012

See more:

SQL

I am having a difficult time with this subject. I have a table that contains a column with nvarchar(MAX) string text in it which is separated by the Pipe (|). The number of variable in the text string is not static. So what I am looking to do is either one of the following:

1. Split the string at the Pipe, into multiple rows for each Pipe encountered in the string.

Or,

2. Split the string at the Pipe, into multiple columns for each Pipe encountered in the string.

Any Ideas? A sample of the string is as follows:

SKILLED NURSING OBSERVATIONS + ADL OF DOE, JANE (1234) COLLECTED 1/22/2016 14:26:58 BY D. DUCK (RN) (COMPLETE)|VITAL SIGNS/I&O/BLOOD GLUCOSE ... [Temperature] Temperature: 96.3 ... VS Temperature Route: Axillary ... [Heart Rate] Radial Heart Rate: 82 ... VS HR Apical Pulse Rhythm: Regular ... VS HR Radial Pulse Character: Normal ... [BP] Systolic BP: 112 ... Diastolic BP: 78 ... [Respiratory] Respirations: 18 ... VS Pulse Ox Oxygen Source: Room Air ... [Pedal Pulses] VS Pedal R Pulse (+): +2- Normal ... VS Pedal L Pulse (+): +2- Normal ||CARDIOVASCULAR ... [Edema] Cardiovascular Edema: None ... [Circulation Checks] Circulation Checks WNL ... [Capillary Refill] Capillary Refill (Seconds): 3sec ||RESPIRATORY ... [Breath Sounds Left Lung] Breath Sounds Left Lung Clear ... [Breath Sounds Right Lung] Breath Sounds Right Lung Clear ... [Respiratory Character] Respiratory Character Regular ||PAIN ... [Pain] Pain Location: No Pain ||MENTAL STATUS / COGNITION ... [Current Mental Status] Mental Status LOC Current Alert ... [Orientation] Mental Status Orientation: x 2 ... [Communication] Mental Status Communication Verbal ||BLADDER ... [Bladder] Urine Clear/Yellow ... Urinary Complaints None ... [Continence] Urinary Continence Incontinence ||GASTROINTESTINAL ... [Gastrointestinal] GI Bowel Sounds Present x 4 ... Gastrointestinal Rectal Bleeding: None ... [Bowel Continence] GI Bowel Continence Incontinent ... [Date of Last BM] Date of Last BM: 01/22/2016 ... [Stool Color] Gastrointestinal Stool C Color: WNL ||NEUROLOGIC ... [Neurologic] Neurologic WNL for Resident ... [Pupils] EENT PERRLA ||SKIN ... [Skin] Skin Intact ... Skin Normal Turgor ... [Skin Issues] Skin Issues No Skin Issues ... Skin Issues Mucous Membranes: Moist ... Skin Issues Prevention Measures In Place: Yes ... [Mattress/Cushion] Skin Pressure Redistribution Mattress ||NUTRITION/ENTERAL ... [Nutrition] Nutritional No Problems Noted ... Nutrition Complains of Thirst: No ... [Diet Type] Nutrition Mechanical Soft Meats ... [Liquids] Nutrition Liquids Thin Liquids ||MOOD AND BEHAVIOR/PSYCHO/SPIRITUAL ... [Mood] Mood/Behavior Mood Pleaseant ... [Psycho/Spiritual] Mood/Behavior Psychospiritual Needs are met ||FUNCTIONAL ... [Functional] Functional Gait Unsteady ... Functional Poor Balance: Yes ... Functional Therapy PT ... Functional Therapy OT ... Functional Therapy ST ... [Mobility] Functional Mobility Activity: Up to Chair ... Functional Mobility Activity Preference: Group Activity ... [Positioning] Functional Positioning: Turn Schedule ... [Devices] Functional Assistive Device Wheelchair ||ACTIVITIES OF DAILY LIVING (ADL) ... [ADLs] Bed Mobility Self-Performance: Extensive assistance ... Bed Mobility Support Provided: One person physical assist ... Transfer Self-Performance: Extensive assistance ... Transfer Support Provided: One person physical assist ... Walk in room Self-Performance: Total dependence ... Walk in room Support Provided: One person physical assist ... Walk in corridor Self-Performance: Total dependence ... Walk in corridor Support Provided: One person physical assist ... Locomotion on unit Self-Performance: Total dependence ... Locomotion on unit Support Provided: One person physical assist ... Locomotion off unit Self-Performance: Total dependence ... Locomotion off unit Support Provided: One person physical assist ... Dressing Self-Performance: Total dependence ... Dressing Support Provided: One person physical assist ... Eating Self-Performance: Extensive assistance ... Eating Support Provided: One person physical assist ... Toilet use Self-Performance: Total dependence ... Toilet use Support Provided: One person physical assist ... Personal hygiene Self-Performance: Total dependence ... Personal hygiene Support Provided: One person physical assist ... Bathing Self-Performance: Total dependence ... Bathing Support Provided: One person physical assist ... [Balance During Transitions and Walking] Moving from seated to standing steady, only able to stabilize with staff assistance ... Walking (with assistive device if used): Not steady, only able to stabilize with staff assistance ... Turning around: Not steady, only able to stabilize with staff assistance ... Moving on and off toilet: Not steady, only able to stabilize with staff assistance ... Surface-to-surface transfer: Not steady, only able to stabilize with staff assistance ||SAFETY ... [Siderails/Bed] Safety Siderails/Bed Low Bed ... [Call Light] Safety Call Light in Reach ... [Notes] Safety Notes: fall precaution ||SPECIAL CARE ... [Isolation Precautions] Special Care Isolation Precautions Standard ... [Hospice] Special Care Hospice

What I have tried:

Have tried looking at Split Functions but don't see where it applies to dynamic length.