My source is Oracle Database. One of my source columns is having data as shown below. I should search for contact: and get the 14 characters for all occurrences.
Source Column data:
contact:"abcd"/Location:"India"/contact:"efgh"/Gender:"Male"/contact:"ijkl"/email:"ijkl@gmail.com"
and so on....
Required output:
contact:"abcd",contact:"efgh",contact:"ijkl"
Please find the below query for your problem. I have written this query in Tera data, if you are using other data base please change the functions accordingly.
I have followed the approach to remove the data which is not related to contact from the data set.
with tab as(select 'contact:"abcd"/Location:"India"/contact:"efgh"/Gender:"Male"/contact:"ijkl"/email:"ijkl@gmail.com"' as val)
select oreplace(oreplace(oreplace(val, substr(val,index(val,'/Location:'),index(val,'/Location:')+3),','),(substr(oreplace(val, substr(val,index(val,'/Location:'),index(val,'/Location:')+3) ,','),
index(oreplace(val, substr(val,index(val,'/Location:'),index(val,'/Location:')+3) ,','),'/Gender:'),
index(oreplace(val, substr(val,index(val,'/Location:'),index(val,'/Location:')+3) ,','),'/Gender:')-15)),','),
substr(val,CHARACTER_LENGTH(val)-22),'') as output from tab;
