asked
Complicated Pattern Match in SQL Server 2008
Hello Techie, someone please share their expertise on this complicated pattern match. DDL: DECLARE @MASTERAPP TABLE ( TYPE VARCHAR (50), Respiration_notes VARCHAR (50) ) INSERT @MASTERAPP SELECT 'LUNGSLINE', '20 Inch-OE Size; P-275-55-20; 109S' UNION ALL-- INVALID REMARKS_2 SELECT 'LUNGSLINE', '20 Inch-OE Size; P-275-55-20; 109S' UNION ALL SELECT 'LUNGSLINE', '20 Inch-Plus Size; P-275-55-20; 113S; 9.5' UNION ALL SELECT 'LUNGSLINE', '20 Inch-Plus Size; Q-888-XX-20; 113S; 9.5;Maleward,bed no 16' -- INVALID Remarks_1_Value DECLARE @CHILD TABLE ( TYPE VARCHAR (50), Remarks_1 VARCHAR (50), Remarks_1_Value VARCHAR (50), Remarks_2 VARCHAR (50), Remarks_2_Value VARCHAR (50) ) INSERT @CHILD SELECT 'LUNGSLINE', 'LUNGS Metric', 'P-275-55-20', 'LungsService', '111T' UNION ALL SELECT 'LUNGSLINE', 'LUNGS Metric', 'P-275-55-20', 'LungsService', '117S' UNION ALL SELECT 'LUNGSLINE', 'LUNGS Metric', 'P-275-55-20', 'LungsService', '111S' i am trying to check whether Respiration_notes contain correct value or not **Respiration_notes** should contain same value as in Remarks_1_Value present after first semicolon and Remarks_2_Value present after second semicolon in **Respiration_notes.** common key between two table is Type. Appreciate your suggestion as i am not sure if it is possible in sql.
so - avoiding all talk of normalising to 1st normal form, what you could do is create a persisted calculated field(s) alter table MASTERAPP add col1 as substring(respiration_notes,charindex(';',respiration_notes),0) alter table MASTERAPP add col2 as substring(respiration_notes,charindex(';',respiration_notes,charindex(';',respiration_notes)) etc etc
19 People are following this question.
