sql server - SQL - How to extract a 4 digit number between two underscores in a cell -

I have the fields below where I need to draw 4 digit numbers between the two underscores, where the two underscores are between Is only a 4 digit number? If there should be a zero

These examples are:

  Cell Acvi_agst 20l9_integreted Imdiam_3l83_ PROS_GEN_20110919 COO_COOLOL Nughlatr_4868_aram_20l40327 PIP_PIP TACTICAL_PIP logical LOOK_4877_PIP_NON_RIP_20140326 IP Warming_lal0l0l0l0l7-Split (2) toomuch_12345_toomuch  

and I hope

  id 3183 4868 4877 nail null  

As you can see, 4 digit code It can be anywhere within the cell, after the second underscores or the third underscores, etc. Oga.

How do I come around this one?

Thank you!

Try it out.

  DECLARE @ cell_1 VARCHAR (500) = 'HV_August 2019 Integrated EM_DAM_3183_PROS_GEN_20110919COO_COOL'; DECLARE @ cell_2 VARCHAR (500) = 'COOL_Newsletter_4868_RAM_20140327 PIP_PIP TACTICAL_PIP TACTICAL'; DECLARE @ cell_3 VARCHAR (500) = 'LOOK_4877_PIP_NON_RIP_20140326'; In PATINDEX, underscore is a special character so that you have to put them in the bracket - then select any number [0- 9] SELECT SUBSTRING (cell, pattiedx '% [_] [0-9] [0- 9] [0- 9] [0-9] [_]% [cell] + 1,4) from digit_code (SELECT @ cell_1 as cell union all SELECT @ cell_2 UNION ALL SELECT @ cell_3) A; < / Code> 

Result:

  digit_code ---------- 3183 4868 4877