regex - Extract nested fields in sql using REGEXP_EXTRACT -


i using google big query , querying sample records column in table:

age=18;cntry=us;coid=9911718;csize=c;func=ops;gdr=f;grp=2099628;grp=85824;grp=1548357;grp=88799;grp=2059383;grp=1937629;ind=78;lang=en;mod=0;occ=511;optout=false;reg=21;s=0;seg=9001;seg=761;seg=541;seg=521;seg=1068;seg=557;seg=546;seg=514;seg=504;seg=183;seg=263;sub=0;tile=1;tile_p=1;title=ic;u=nql8uz5qrt8vcqh5tkcqq697 

query:

select regexp_extract(col,r'age=(\d+)') age,   regexp_extract(col,r'cntry=(\d+)') country,   regexp_extract(col,r'gdr=(\d+)') gender table x 

result: 18 null null

i getting age value other values null. great on this.

the cntry , gdr values not numeric, consist of letters.

you may use \w+ matching 1 or more "word" chars, i.e. letters, digits , underscores:

select regexp_extract(col,r'age=(\d+)') age,   regexp_extract(col,r'cntry=(\w+)') country,   regexp_extract(col,r'gdr=(\w+)') gender table x 

Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -