Delete new lines based on search criteria


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Delete new lines based on search criteria
# 1  
Old 01-19-2010
Delete new lines based on search criteria

Hi all!

A bit of background: I am trying to create a script that formats SQL statements. I have gotten so far as to add new lines based on certain match criteria like commas, keywords etc. In the process, I end up adding newlines where I don't want.

For example: substr(colName, 1, 10) becomes:
substr(colName,
1,
10) - I don't want this particular comma to be replaced with a newline.

With that in mind, my questions are:

1. Is there a way I can ignore the commas within braces - ( )
2. If not, how can I delete the newlines on lines starting with numbers, i.e. look for numbers at the beginning of a line and remove the newline before it.

In other words, if my input is:
substr(colName,
1,
10)

my output should be: substr(colName, 1, 10).

Any help is really appreciated! Thanks in advance.
Jay.

---------- Post updated 01-19-10 at 12:05 AM ---------- Previous update was 01-18-10 at 11:13 PM ----------

Another thing I forgot to mention: can I make the script intelligent enough to recognize negative numbers as well? Another example along the lines of the substr above is:

add_months(somedate, -6)
is becoming
add_months(somedate,
-6).

Don't want this to happen either.
# 2  
Old 01-19-2010
Hope I got your question correctly, But I used the different approach to solve this issue ( i,e I didnt check if the new line appears before the line starts with the number ). It works for me. You can try this out.
Code:
$cat inputfile
substr(colName,
1,
10)
add_months(somedate,
-6)

Code:
$ sed '
 :loop
 $!N
 s/\(.*,\)\n\(.*,\)/\1\2/
 /\(.*\)\n\(.*)\)/{
 s/\(.*\)\n\(.*)\)/\1\2/
 p;d;
 }
 t loop' inputfile
substr(colName,1,10)
add_months(somedate,-6)

# 3  
Old 01-19-2010
Code:
awk '{if ($0~ /\(/ && $0 ~ /\)/) {print $0} else {sub(","," \n",$0);print }}' infile

HTH,
PL
# 4  
Old 01-19-2010
Quote:
Originally Posted by daptal
Code:
awk '{if ($0~ /\(/ && $0 ~ /\)/) {print $0} else {sub(","," \n",$0);print }}' infile

HTH,
PL
Thanks for your response, daptal. There seems to be a bit of a problem though. I am on a solaris machine, by the way. When I run your awk statement, I get an error:

awk: syntax error near line 1
awk: illegal statement near line 1

When I use nawk or gawk though, the command does run successfully, but does the opposite of what I was expecting, i.e. adds an extra newline instead of the comma. My output in these two cases was:

substr(colName

1

10)
add_months(somedate

-6)

Thanks!

---------- Post updated at 01:36 AM ---------- Previous update was at 01:26 AM ----------

Quote:
Originally Posted by skmdu
Hope I got your question correctly, But I used the different approach to solve this issue ( i,e I didnt check if the new line appears before the line starts with the number ). It works for me. You can try this out.
Code:
$cat inputfile
substr(colName,
1,
10)
add_months(somedate,
-6)

Code:
$ sed '
 :loop
 $!N
 s/\(.*,\)\n\(.*,\)/\1\2/
 /\(.*\)\n\(.*)\)/{
 s/\(.*\)\n\(.*)\)/\1\2/
 p;d;
 }
 t loop' inputfile
substr(colName,1,10)
add_months(somedate,-6)

Thanks for your response, skmdu! This code works when my input is just the examples that I had quoted in my original post. There is a bit of a problem, though - and it is entirely because I didn't give you the entire picture. Here's a more complete input:

My input (if you notice, its all in one line):

Code:
SELECT  C.column1,  A.column4,  (SUBSTR(A.column8, 1, 2)),  (SUBSTR(A.column9, 3, 1)),  CASE WHEN (SUBSTR(A.column9, 3, 1) = 'V') THEN B.column1 ELSE SUBSTR(A.column9, 1, 2) END,  B.column2 FROM  TABLE1 A,  TABLE2 B,  TABLE3 C,  TABLE4 D,  TABLE5 E WHERE (((((A.column3 = C.column3) AND (A.column4 = B.column4)) AND (A.column5 = D.column5)) AND (A.column6 = E.column6)) AND (A.column6 IN ('1', '2')));

My output so far (without your sed statement):

Code:
SELECT
  C.column1,
  A.column4,
  (SUBSTR(A.column8,
 1,
 2)),
  (SUBSTR(A.column9,
 3,
 1)),
  CASE
WHEN (SUBSTR(A.column9,
 3,
 1) = 'V') THEN B.column1
ELSE SUBSTR(A.column9,
 1,
 2)
END,
  B.column2
FROM
  TABLE1 A,
  TABLE2 B,
  TABLE3 C,
  TABLE4 D,
  TABLE5 E
WHERE (((((A.column3 = C.column3)
AND (A.column4 = B.column4))
AND (A.column5 = D.column5))
AND (A.column6 = E.column6))
AND (A.column6 IN ('1',
 '2')));

Output when your sed statement is included in my script that produced the above output:
Code:
SELECT
  C.column1,
  A.column4,  (SUBSTR(A.column8, 1, 2)),  (SUBSTR(A.column9, 3, 1)),
  CASE
WHEN (SUBSTR(A.column9, 3, 1) = 'V') THEN B.column1
ELSE SUBSTR(A.column9, 1, 2)
END,
  B.column2
FROM
  TABLE1 A,
  TABLE2 B,  TABLE3 C,  TABLE4 D,
  TABLE5 E
WHERE (((((A.column3 = C.column3) AND (A.column4 = B.column4))
AND (A.column5 = D.column5)) AND (A.column6 = E.column6))
AND (A.column6 IN ('1', '2')));,

Notice how some lines are joined because of extra commas or braces being present on a specific line. Also, an additional comma was added at the end, after the semi-colon.

Again, I realize it was because I had not given you much to work with to begin with. Thanks again for your help so far! Really, really appreciate it.

- Jay.

Last edited by jayarkay; 01-19-2010 at 02:44 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Delete duplicate row based on criteria

Hi, I have an input file as shown below: 20140102;13:30;FR-AUD-LIBOR-1W;2.495 20140103;13:30;FR-AUD-LIBOR-1W;2.475 20140106;13:30;FR-AUD-LIBOR-1W;2.495 20140107;13:30;FR-AUD-LIBOR-1W;2.475 20140108;13:30;FR-AUD-LIBOR-1W;2.475 20140109;13:30;FR-AUD-LIBOR-1W;2.475... (2 Replies)
Discussion started by: shash
2 Replies

2. Shell Programming and Scripting

Copying section of file based on search criteria

Hi Guru's, I am new to unix scripting. I have a huge file with user details in it(file2) and I have another file with a list of users(file1). Script has to search a user from file1 and get all the associated lines from file2. Example: fiel1: cn=abc cn=DEF cn=xyx File 2: dn:... (10 Replies)
Discussion started by: Samingla
10 Replies

3. Shell Programming and Scripting

Need To Delete Lines Based On Search Criteria

Hi All, I have following input file. I wish to retain those lines which match multiple search criteria. The search criteria is stored in a variable seperated from each other by comma(,). SEARCH_CRITERIA = "REJECT, DUPLICATE" Input File: ERROR,MYFILE_20130214_11387,9,37.75... (3 Replies)
Discussion started by: angshuman
3 Replies

4. Shell Programming and Scripting

Select lines from a file based on a criteria

Hi I need to select lines from a txt file, I have got a line starting with ZMIO:MSISDN= and after a few line I have another line starting with 'MOBILE STATION ISDN NUMBER' and another one starting with 'VLR-ADDRESS' I need to copy these three lines as three different columns in a separate... (3 Replies)
Discussion started by: Tlcm sam
3 Replies

5. Shell Programming and Scripting

Merging Lines based on criteria

Hello, Need help with following scenario. A file contains following text: {beginning of file} New: This is a new record and it is not on same line. Since I have lost touch with script take this challenge and bring all this in one line. New: Hello losttouch. You seem to be struggling... (4 Replies)
Discussion started by: losttouch
4 Replies

6. Shell Programming and Scripting

Extract data based on specific search criteria

I have a huge file (about 2 millions records) contains data separated by “,” (comma). As part of the requirement, I can't change the format. The objective is to remove some of the records with the following condition. If the 23rd field on each line start with 302 , I need to remove that from the... (4 Replies)
Discussion started by: jaygamini
4 Replies

7. Shell Programming and Scripting

awk search & delete located criteria

Guys, I manages to get awk to search and print the files that I want to delete. However I am stuck on the delete portion. Here is the command that I am using to fins these files. find /usr/local/apache/conf/vhosts/ -type f | awk '/e$/' The output is perfect. The files look like so: ... (4 Replies)
Discussion started by: jaysunn
4 Replies

8. Shell Programming and Scripting

Append specific lines to a previous line based on sequential search criteria

I'll try explain this as best I can. Let me know if it is not clear. I have large text files that contain data as such: 143593502 09-08-20 09:02:13 xxxxxxxxxxx xxxxxxxxxxx 09-08-20 09:02:11 N line 1 test line 2 test line 3 test 143593503 09-08-20 09:02:13... (3 Replies)
Discussion started by: jesse
3 Replies

9. Shell Programming and Scripting

remove lines based on score criteria

Hi guys, Please guide for Solution. PART-I INPUT FILE (has 2 columns ID and score) TC5584_1 93.9 DV161411_2 79.5 BP132435_5 46.8 EB682112_1 34.7 BP132435_4 29.5 TC13860_2 10.1 OUTPUT FILE (It shudn't contain the line ' BP132435_4 29.5 ' as BP132435 is repeated... (2 Replies)
Discussion started by: smriti_shridhar
2 Replies

10. UNIX for Dummies Questions & Answers

Select records based on search criteria on first column

Hi All, I need to select only those records having a non zero record in the first column of a comma delimited file. Suppose my input file is having data like: "0","01/08/2005 07:11:15",1,1,"Created",,"01/08/2005" "0","01/08/2005 07:12:40",1,1,"Created",,"01/08/2005"... (2 Replies)
Discussion started by: shashi_kiran_v
2 Replies
Login or Register to Ask a Question