0

I want to beautify the SQL code using this tool. Before this tool will accept it, I want to replace each instance of "<*>" (excluding double quotes) with "\n\n\n\n\n" (5 new lines) in TextPad using the Replace feature and choosing Regular Expression. How can I do this?

enter image description here

http://www.dpriver.com/pp/sqlformat.htm

insert into i2b2demodata.QUERY_GLOBAL_TEMP ( patient_num , panel_count) select patient_num ,1 as panel_count from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Reports\%') AND ( valtype_cd = 'B' AND contains(observation_blob,'Arthritis') > 0 ) group by patient_num ) t <*> insert into i2b2demodata.DX ( patient_num ) select * from ( select distinct patient_num from i2b2demodata.QUERY_GLOBAL_TEMP where panel_count = 1 ) q <*> insert into i2b2demodata.MASTER_QUERY_GLOBAL_TEMP(master_id, patient_num, level_no) select 'masterid:22', patient_num, 0 from i2b2demodata.QUERY_GLOBAL_TEMP where patient_num IN ( select patient_num from i2b2demodata.QUERY_GLOBAL_TEMP where panel_count = 1 ) <*> delete i2b2demodata.DX <*> delete i2b2demodata.QUERY_GLOBAL_TEMP <*> insert into i2b2demodata.QUERY_GLOBAL_TEMP ( patient_num , panel_count) select patient_num ,1 as panel_count from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Diagnoses\Digestive system (520-579)\%') group by patient_num ) t <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count = 2 where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count = -1 where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 2 and exists ( select 1 as panel_count from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Diagnoses\Circulatory system (390-459)\%') group by patient_num having count( distinct patient_num || '|' || encounter_num || '|' || provider_id || '|' || instance_num || '|' ||concept_cd || '|' ||cast(start_date as varchar(50) ) ) >= 3 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num group by patient_num ) <*> insert into i2b2demodata.DX ( patient_num ) select * from ( select distinct patient_num from i2b2demodata.QUERY_GLOBAL_TEMP where panel_count = 2 ) q <*> delete i2b2demodata.MASTER_QUERY_GLOBAL_TEMP where master_id = 'masterid:22' and level_no >= 1 <*> <*> insert into i2b2demodata.MASTER_QUERY_GLOBAL_TEMP(master_id, patient_num, level_no) select 'masterid:21', patient_num, 0 from i2b2demodata.QUERY_GLOBAL_TEMP where patient_num IN ( select patient_num from i2b2demodata.QUERY_GLOBAL_TEMP where panel_count = 2 ) <*> delete i2b2demodata.DX <*> delete i2b2demodata.QUERY_GLOBAL_TEMP <*> insert into i2b2demodata.QUERY_GLOBAL_TEMP ( patient_num , panel_count) select patient_num ,1 as panel_count from ( select patient_num from i2b2demodata.MASTER_QUERY_GLOBAL_TEMP where master_id = 'masterid:22' group by patient_num ) t <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =2 where exists ( select 1 as panel_count from ( select patient_num from i2b2demodata.MASTER_QUERY_GLOBAL_TEMP where master_id = 'masterid:21' group by patient_num ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> insert into i2b2demodata.DX ( patient_num ) select * from ( select distinct patient_num from i2b2demodata.QUERY_GLOBAL_TEMP where panel_count = 2 ) q
JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245
  • You're finding on the regex <*> which will either find all closing brackets (>) or open bracket followed immediately by a closing bracket (<>). That's because the * means to find 0 or more of the character right before it. Lastly, does this have to be done with TextPad? I'm pretty sure it will be trivial to do in Notepad++. To help me understand what you want, can you show text before, text after? – Elijah W. Gagne Jul 12 '12 at 01:27
  • see the word "instance". Replace each instance of the first quoted string with the new quoted string. – JustBeingHelpful Jul 12 '12 at 02:17

1 Answers1

1

The regex to match <*> is <\*>.

Johnny Everson
  • 8,343
  • 7
  • 39
  • 75
  • but the result is the literal string "\n\n\n\n\n", not 5 new lines. – JustBeingHelpful Jul 12 '12 at 02:19
  • I think that is a limitation of your text editor. It is not a problem with the regex. You can try to copy 'new file' from the text edit and paste it on the replace field. It will show as blank field. – Johnny Everson Jul 12 '12 at 03:50
  • I guess the first sentence is correct in your answer, and the second sentence is incorrect. Please change it to replace "<*>" with "<\\*>" and I'll mark it as correct. – JustBeingHelpful Jul 12 '12 at 11:08