Formating questions

Hi,

I have a data as follows in some files, i want to change CHAR(2-20) to VARCHAR(2-20). I should not touch any line with CHAR(1)

Example:

Input:

     cur_rev_stage_cd CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      prev_rev_stage_cd CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      revrec_prcs_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      revrec_cur_rev_stage_cd CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      revrec_prev_rev_stage_cd CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      svc_base_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      pkging_type_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      bus_tran_type_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      dvc_nm CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      cust_intln_cd CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      cash_only_flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      shpmt_type_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

Result should be given below:

      cur_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      prev_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      revrec_prcs_cd VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      revrec_cur_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      revrec_prev_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      svc_base_cd VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      pkging_type_cd VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
      bus_tran_type_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      dvc_nm VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
      cust_intln_cd VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
      cash_only_flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
      shpmt_type_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

Could this ?

$ awk -F'[(|)]' '$2>=2 && $2<=20{gsub(/CHAR/,"VARCHAR",$1);$1=$1"("$2")";$2=""}1'  file

Please use code tags as required by forum rules!

Try this

awk '{gsub (/CHAR\([^1]|[12][0-9]\)/,"VAR&")}1' file

Hi Rudi,

Thank you for your quick response.

We have some data as CHAR(10) and CHAR(15) as well in the data. We can to change that also VARCHAR(10) and VARCHAR(15) respectively. Kindly suggest ?

Regards,
Srikanth

---------- Post updated at 10:37 PM ---------- Previous update was at 10:33 PM ----------

If i use below command:

awk '{gsub (/CHAR\([^1]|[12][0-9]\)/,"VAR&")}1' file

Its changing

pkg_trkng_uniq_id CHAR(VAR10) CHARACTER SET LATIN NOT CASESPECIFIC,

But i want it as follows:

pkg_trkng_uniq_id VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,

Regards,
Srikanth

I thought I had tested that as well. Rats! Try this:

awk '{gsub (/CHAR\(([^1]|[12][0-9])\)/,"VAR&")}1' file
cur_rev_stage_cd VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
prev_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
revrec_prcs_cd VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
revrec_cur_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
revrec_prev_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
svc_base_cd VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
pkging_type_cd VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
bus_tran_type_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
dvc_nm VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
cust_intln_cd VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
cash_only_flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
shpmt_type_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
1 Like

@srikanth38

You didn't try #2 ?
Assumed that field1 contains only CHAR where Field Separator is (|)

if only CHAR(10)

$ awk -F'[(|)]' '$2==10{gsub(/CHAR/,"VARCHAR",$1);$1=$1"("$2")";$2=""}1' file

Thank you Rudi. Appriciate you help. It worked fine.

Hello,

Following may also help, it will not see the the CHAR(2-20) it will only look CHAR( and accordingly will give as requested output.

awk '/CHAR\(1\)/ {print} !/CHAR\(1\)/ {gsub(/CHAR\(/,"VARCHAR\("); print}'  file_name

Output wil lbe as follows.

cur_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
prev_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
revrec_prcs_cd VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
revrec_cur_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
revrec_prev_rev_stage_cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
svc_base_cd VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
pkging_type_cd VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC,
bus_tran_type_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
dvc_nm VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
cust_intln_cd VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
cash_only_flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
shpmt_type_cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

Thanks,
R. Singh