-1

for eg:

CREATE TABLE MWWDATA.ACK997 (
    AKTYPE CHAR(2) DEFAULT ''  NOT NULL ,
    AKNUM CHAR(9) DEFAULT ''  NOT NULL );

CREATE TABLE MWWDATA.APREIDEXC (
    EMPLID NUMBER(15, 0) DEFAULT NULL );

I want output like:

CREATE TABLE MWWDATA.ACK997(AKTYPE,ANUM);
CREATE TABLE MWWDATA.APREIDEXC(EMPLID);
4
  • This feels like an XY problem. What, exactly, is it that you want to do? You almost certainly don't need to remove the types from this SQL command line, but maybe might need to massage an export from a database into some other format. There are specialized tools for that, but to give you hints on that, we'd need more information... – Wouter Verhelst Nov 2 '15 at 14:04
  • First of all, please show us what you've tried and how it failed so we don't reinvent the wheel. Second, where is the comma you mention? Third, please edit and explain clearly, in the question, what you need to do. – terdon Nov 2 '15 at 14:07
  • The command i used: awk '{if (NR==1) {printf $0} else {printf $1","}} END {printf "^H);\n"}' inputfile The output i got: CREATE TABLE MWWDATA.ACK997 (AKTYPE,AKNUM,,,,,,CREATE,EMPLID,,,,^H); – TOM Nov 2 '15 at 14:12
  • What I wanted to do is print the line that starts with "create table" word and rest of the line that is in between " create table and semicolon(;) " prints only first word. – TOM Nov 2 '15 at 14:44
0

The way

As is a lot lighter than mosts alternatives, there is another solution

sed -ne '
    /CREATE/{h;d};
    s/^ *\([^ ]\+\) .*\()\);\? *$/\1\2/;
    ta;
    s/^ *\([^ ]\+\) .*$/\1/;
    H;
    bb;
   :a;
    H;
    x;
    s/\n//;
    s/\n/,/g;
    s/ (/(/;p ;
   :b'

This could be written:

sed -ne '/CREATE/{h;d};s/^ *\([^ ]\+\) .*\()\);\? *$/\1\2/;ta;
    s/^ *\([^ ]\+\) .*$/\1/;H;bb;:a;H;x;s/\n//;s/\n/,/g;s/ (/(/;p ;:b'

This will render, from you sample

CREATE TABLE MWWDATA.ACK997(AKTYPE,AKNUM)
CREATE TABLE MWWDATA.APREIDEXC(EMPLID)
1
  • Thnx, it is working.... – TOM Nov 3 '15 at 9:42
0

This perl script

  • prints the full line (without a \n) if it matches /^CREATE TABLE/
  • the first word followed by ); and a newline if the line ends with ;
  • and the first word followed by a , if the line ends with a ,.
  • All other input lines are ignored.
#! /usr/bin/perl

use strict;

while(<>) {
   chomp;
   if (m/^CREATE TABLE/) {
       print $_;
   } else {
        my @line=split ;
        if (/;\s*$/) {
            print "$line[0]);\n";
        } elsif (m/,\s*$/) {
            print "$line[0],";
        }
   }
}

The script's output matches what you asked for:

$ ./summarise.pl table.sql 
CREATE TABLE MWWDATA.ACK997 (AKTYPE,AKNUM);
CREATE TABLE MWWDATA.APREIDEXC (EMPLID);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.