curtisb has asked for the wisdom of the Perl Monks concerning the following question:

I would like to thank everyone who has help me with some of the problems I presented. Thanks!
Now, I have another problem. This one is a bit more tricky, to me anyways. Hopefully, someone will have a good, simple solution to this problem.

I have this in an SQL file, which was taken from a Sybase DB table structure.
CREATE TABLE "ARSAMSUSER"."LINK_TYPE_CODE"
(
    LINK_TYPE             VARCHAR2(20) NOT NULL,
    DCD_NO                VARCHAR2(1) NULL,
    IS_ONE_TO_ONE         VARCHAR2(1) NULL CHECK(IS_ONE_TO_ONE IS NULL
+ OR(IS_ONE_TO_ONE IN(Y,N,NULL))),
    IS_DRAWN              VARCHAR2(1) NULL CHECK(IS_DRAWN IS NULL OR(I
+S_DRAWN IN(Y,N,NULL))),
    DESCRIPTION           VARCHAR2(60) NULL,
    LENGTH_OPERATION          VARCHAR2(4) NULL,
    WIDTH_OPERATION           VARCHAR2(4) NULL,
    HEIGHT_OPERATION          VARCHAR2(4) NULL,
    WEIGHT_OPERATION          VARCHAR2(4) NULL,
    AREA_OPERATION            VARCHAR2(4) NULL,
    PRIMARY KEY (LINK_TYPE) 
)
TABLESPACE ARSAMS
/

[download]
What I need to do to this block of code is this. Starting from the word CHECK and ending at the comma (,). I either need to place this in the row directly above the PRIMARY KEY line or simply on the next line under IS_ONE_TO_ONE. I need to make the break between the NULL and the CHECK. Only problem is that I'm not sure on how to do this one. If I do 
s/\CHECK\b//i;

[download]
All I do is delete CHECK. Could someone please guide me in the right direction.

Example of what I need it to look like.
CREATE TABLE "ARSAMSUSER"."LINK_TYPE_CODE"
(
    LINK_TYPE             VARCHAR2(20) NOT NULL,
    DCD_NO                VARCHAR2(1) NULL,
    IS_ONE_TO_ONE         VARCHAR2(1) NULL,
    CHECK(IS_ONE_TO_ONE IS NULL OR(IS_ONE_TO_ONE IN(Y,N,NULL))),
    IS_DRAWN              VARCHAR2(1) NULL,
    DESCRIPTION           VARCHAR2(60) NULL,
    LENGTH_OPERATION          VARCHAR2(4) NULL,
    WIDTH_OPERATION           VARCHAR2(4) NULL,
    HEIGHT_OPERATION          VARCHAR2(4) NULL,
    WEIGHT_OPERATION          VARCHAR2(4) NULL,
    AREA_OPERATION            VARCHAR2(4) NULL,
    CHECK(IS_DRAWN IS NULL OR(IS_DRAWN IN(Y,N,NULL))),
    PRIMARY KEY (LINK_TYPE) 
)
TABLESPACE ARSAMS
/

[download]
Thanks again for all the help. Hope to hear from all of you soon.

Bobby

edited: Sat Sep 28 03:10:07 2002 by jeffa - title change (was: Yet another problem)

Replies are listed 'Best First'.
Re: Yet another problem
by Enlil (Parson) on Sep 27, 2002 at 19:35 UTC
    This works: 
    while ( <DATA> )
{
    $_ =~ s/( CHECK\(.*,)/,\n   \1/;
    #Find a space followed by a
    #CHECK followed by a (
    #and then followed by anything but
    #having to be followed by a ,
    #and substituting with comma a
    #new line and then anything that was
    #matched previously between the parens
    #the () not the \()
    print $_;
}


__DATA__

CREATE TABLE "ARSAMSUSER"."LINK_TYPE_CODE"
(
    LINK_TYPE             VARCHAR2(20) NOT NULL,
    DCD_NO                VARCHAR2(1) NULL,
    IS_ONE_TO_ONE         VARCHAR2(1) NULL CHECK(IS_ONE_TO_ONE IS NULL
+ OR(IS_ONE_TO_ONE IN(Y,N,NULL))),
    IS_DRAWN              VARCHAR2(1) NULL CHECK(IS_DRAWN IS NULL OR(I
+S_DRAWN IN(Y,N,NULL))),
    DESCRIPTION           VARCHAR2(60) NULL,
    LENGTH_OPERATION          VARCHAR2(4) NULL,
    WIDTH_OPERATION           VARCHAR2(4) NULL,
    HEIGHT_OPERATION          VARCHAR2(4) NULL,
    WEIGHT_OPERATION          VARCHAR2(4) NULL,
    AREA_OPERATION            VARCHAR2(4) NULL,
    PRIMARY KEY (LINK_TYPE)
)
TABLESPACE ARSAMS
/

    [download]
    But I am making assumptions based on this one piece of data. all s/CHECK\b//i does is check for the CHECK (case insensitive)followed by word boundary and then replace it with nothing (as you mentioned).
[reply]
[d/l]

      Ahem! Shouldn't that \1 be $1?

      Cor! Like yer ring! ... HALO dammit! ... 'Ave it yer way! Hal-lo, Mister la-de-da. ... Like yer ring!
[reply]
        It can be I guess, but it will work just as well as \1. Outside of the s/// operator I must use $1 but while I am inside it I can use $1 or \1, \2 or $2...

        Update: Even though it works just as well, as per the perlre: "this is a dirty habit to get into" (as it was grandfathered in for sed fans). Sometimes a new trick is not so new nor wise to use without restraint (in this case probably never lest I be working with sed).

        -Enlil

[reply]
Re: Yet another problem
by fglock (Vicar) on Sep 27, 2002 at 19:26 UTC

    simply on the next line under IS_ONE_TO_ONE

    Is this ok?

    s/\bCHECK\b/,\n CHECK /i;
[reply]
[d/l]
      Thanks for the help. Now, how about putting single qoutes (') around the Y and N only. In the same command.
      Thanks
      Bobby
[reply]
        s|\(Y,N,|\('Y','N',|;

        should do it safely, unless you expect to find spaces between these characters (then you must add some \b* in some places).

[reply]
[d/l]

Back to Seekers of Perl Wisdom