Search for a pattern in a file and split the line into two lines

Shell Programming and Scripting
1

Hi All,

Greetings everyone !!!

I have a file which has many lines, out of which one line is as below.

I need to search for pattern "varchar(30) Select" and if exists, then split the line as below.

I am trying to achieve this in ksh. Can anyone help me on this.

2

Hello Pradhikshan,

Could you please try following. Let's say following is Input_file.

cat Input_file
declare @newname varchar(30) Select @newname = filename from table_bck
declare @newname varchar(30) Select @newname = filename from table_bck
declare @newname varchar(30) Select @newname = filename from table_bck

Then following is the code for same.

awk '/varchar\(30\) Select/{gsub(/varchar\(30\) /,"&\n");print}'  Input_file

Output will be as follows.

declare @newname varchar(30)
Select @newname = filename from table_bck
declare @newname varchar(30)
Select @newname = filename from table_bck
declare @newname varchar(30)
Select @newname = filename from table_bck

Thanks,
R. Singh

3

Using Sing's input file with sed!

cat split | sed '/\(.*Select \)\(.*$\)/ {s//\1\n\2/}'

declare @newname varchar(30) Select
 @newname = filename from table_bck
declare @newname varchar(30) Select
 @newname = filename from table_bck
declare @newname varchar(30) Select
 @newname = filename from table_bck

I do have a problem including (30), the issue of parens in sed.
If anybody!?
Fas

4

With a standards conforming version of sed , try:

sed 's/\(varchar[(]30[)]\) \(Select\)/\1\
\2/' Input_file

producing the output:

declare @newname varchar(30)
Select @newname = filename from table_bck
declare @newname varchar(30)
Select @newname = filename from table_bck
declare @newname varchar(30)
Select @newname = filename from table_bck

I believe GNU sed would allow \n instead of the \<newline> shown here, but it probably would also have to be told to conform to the standards with something like:

sed --posix 's/\(varchar[(]30[)]\) \(Select\)/\1\
\2/' Input_file

to get the parenthesized expressions to work correctly. (I don't have a GNU sed available to test at the current time.)

5

Revised sed statement with proper result.

cat split | sed '/\(.*varchar.*[)] \)\(.*$\)/ {s//\1\n\2/}' 

declare @newname varchar(30) 
Select @newname = filename from table_bck
declare @newname varchar(30) 
Select @newname = filename from table_bck
declare @newname varchar(30) 
Select @newname = filename from table_bck

Square brackets being the the parens isolator.

6

Please get rid of the unneeded cat and consider simplifying your sed script as shown in post #4 in this thread.

7

Thanks for the explanation Don.

But mine is slightly different and still works.
Is my solution improper in any way?
Fas

Saw your post after my answer!

---------- Post updated at 03:04 PM ---------- Previous update was at 03:03 PM ----------

Will do!

8

The differences between:

cat split | sed '/\(.*varchar.*[)] \)\(.*$\)/ {s//\1\n\2/}'

and:

sed 's/\(varchar[(]30[)]\) \(Select\)/\1\
\2/' Split

are:

  1. the 1st form reads the data in split twice and writes the data in split twice while the 2nd form reads and writes the data once,
  2. the 1st form uses two processes while the 2nd form uses one process,
  3. the 1st form will transform any line containing the string varchar followed by zero of more arbitrary characters followed by a ) and a <space> while the 2nd form changes the <space> character in the string varchar(30) Select to a <newline> character (as specified in the 1st post in this thread),
  4. the 1st form uses more system resources than the 2nd form,
  5. the 1st form runs slower than the 2nd form, and
  6. the 1st form will work on some systems that change a \n in a sed substitute command replacement string to a <newline> character and on systems with a standards conforming version of sed will print varchar(30)nSelect (note the n instead of the requested <newline>) while the 2nd form should do what you want with any sed .

Also, compare the results you get from the two above scripts with the input:

declare @newname varchar(30) Select @newname = filename from (table_bc)

I assume that your input file won't have anything that has more than one closing parenthesis on a line containing the string varchar , but the 1st form will give you something you don't want if that should ever occur. The 2nd form only transforms the text you said you wanted to transform.

1 Like
9

It is hard to get this level of specifics elsewhere, why I appreciate your time on this. I will dissect the whole line by line!

Perfect for learning since I just started to be interested by sed and it's flexibility.

Thanks Don