Как разделить длинный строковый текст с разделителями в SQL 2012
У меня сейчас трудное время с этим предметом. У меня есть таблица, содержащая столбец со строковым текстом nvarchar (MAX), который разделен трубой (|). Число переменных в текстовой строке не является статическим. Итак, то, что я хочу сделать, - это одно из следующих действий:
1. Разделите строку на трубе на несколько строк для каждой трубы, встречающейся в строке.
Или,
2. разделите строку на трубе на несколько столбцов для каждой трубы, встречающейся в строке.
Есть Идеи? Пример строки выглядит следующим образом:
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
Что я уже пробовал:
Я пробовал смотреть на функции разделения, но не вижу, где это применимо к динамической длине.