question

AlexKlap avatar image
AlexKlap 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.
sql-server-2008t-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
MVDBA avatar image
MVDBA answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@MVDBA: Thanks for your suggestion, but unfortunately i don't have any permission to change DDL. Please suggest.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.