SELECT
brand,
cat_no,
info3 remark,
case
when info3 ~ '\|'
then trim(substring(info3 from '([^\|]+)'))
else null
end purity,
case
when info3 ~* '/kit' and info3 ~ '\|'
then regexp_replace(substring(info3 from '(?<=\|) ?(.+)'), '(?<=\d) ', '')
when info3 ~ '\|'
then regexp_replace(substring(info3 from '(?<=\|) ?([^/]+)'), '(?<=\d) ', '')

when info3 ~ '\d ?x ?\d'
then regexp_replace(info3, '\D*(\d+) ?x ?(\d+(\.\d+)?) ?([mMuUμkK]?[gGlL]).*', '\1x\2\4')

when info3 ~* 'kit'
then replace(info3, 'Analytical Standard', '')
-- when info3 ~ 'Certified Reference Material'
-- then null
else regexp_replace(info3, '\D*(\d+(\.\d+)?) ?([mMuUμkK]?[gGlL]).*', '\1\3')
-- regexp_replace(regexp_replace(regexp_replace(info3,'/[支套]',''), '(?<=\d) ', ''),'^approx ','')
end package,
'瓶' unit_name,
-- info4,
substring(replace(info4, ',', '') from '(\d+(\.\d+)?)')::float orig_cost,
'USD' currency,
stock_info delivery,
create_date created_at,
modify_date modified_at,
'SYSTEM' modified_by
FROM public.rawdata
WHERE brand = 'cerilliant'