Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

I am looking for a bash script. In a text file I have data like:

+------+------
| Id   | User | 
+------+------+
| 8192 | root | 
| 8194 | root |
| 8202 | root |
| 8245 | root | 
| 8434 | root |  
| 8754 | root | 
| 8761 | root | 
| 8762 | root | 
| 8764 | root | 
| 8771 | root | 
+------+------+

I want to extract the data as follow:

8192,8194,8202,8245,8434,8754,8761,8762,8764

I mean, I need first field containing numbers, but not the last one. And all the numbers extracted should be separated by comma (,).

Could somebody help me to get it ?

share|improve this question
    
Where does this come from? SQL? If so why not SELECT GROUP_CONCAT(DISTINCT {fields} ) FROM ... –  Rinzwind Feb 24 at 15:48
    
@Rinzwind yes it is a SQL output. No AFAIK its not from a table. All these are MYSQL sleeping PID which I have to kill. –  AgentCool Feb 24 at 15:56
    
Is this still needed due to Kill Sleeping Process in MySQL ? :D –  Wilf Feb 24 at 16:02
1  
Yes @Wilf , there I can list the data but I can not put it in format because sometimes I may get 100 Connections. 100 ID putting in format by separating with comma , bash can do that very easily rather than me. –  AgentCool Feb 24 at 16:04
    
almost All answers are excellent , which one I have to accept thats big deal. –  AgentCool Feb 24 at 19:04

5 Answers 5

up vote 6 down vote accepted

You don't need a script for such a simple thing. You can use awk:

awk '$2 ~ "^[0-9][0-9]*$" { print $2 }' file.txt | head -n -1 | awk '{print}' ORS=',' | sed 's/,$/\n/'

Some explanations:

  • awk '$2 ~ "^[0-9][0-9]*$" { print $2 }' file.txt - print from the file.txt only the fields which are numbers.
  • head -n -1 - remove last line / last number.
  • awk '{print}' ORS=',' - concatenate all lines in one single line, each number separated by ,.
  • sed 's/,$/\n/' - replace last , with a newline character.

Or, shorter:

awk '$2 ~ "^[0-9][0-9]*$" { print $2 }' ORS=',' file.txt | sed 's/,[0-9]*,$/\n/'
share|improve this answer
    
My brother , Thanks for answering.+1 , How about If i do have IP's because due to security reasons I have not gave you any IP's.IP's are third field. –  AgentCool Feb 24 at 18:15
    
@rajagenupula Just replace { print $2 } with { print $6 } and sed 's/,[0-9]*,$/\n/' with sed 's/,[0-9.]*,$/\n/' –  Radu Rădeanu Feb 24 at 18:58

The most shortest way I can find:

echo `sed 's/[^0-9]//g' your_file` | sed 's/ /,/g'

Result is:

8192,8194,8202,8245,8434,8754,8761,8762,8764,8771

[^0-9] - means everything but numericals

s/[^0-9]//g - removes everything but numericals

Substitute your_file with path to your file


For you finally task, with kill command. Be carefully when using this line, it will kill every PID in list:

for pid in `sed 's/[^0-9]//g' your_file | grep -v '^$'`; do kill -9 $pid;done

Before run previous line, you may want to run this line:

for pid in `sed 's/[^0-9]//g' your_file | grep -v '^$'`; do echo "kill -9 $pid";done

It will show something like this:

kill -9 8192
kill -9 8194
kill -9 8202
kill -9 8245
kill -9 8434
kill -9 8754
kill -9 8761
kill -9 8762
kill -9 8764
kill -9 8771

Good luck!

share|improve this answer
    
you are awesome man , thanks for that such short one. but actually in connection files sometimes I may get IP's also which also numerical. so how you're command can separate them from process IP's ? Thank you –  AgentCool Feb 24 at 18:12
    
Shouldn't you be sending SIGTERMs first? –  Blacklight Shining Feb 24 at 20:53
    
I cannot send SIGTERM to a MySQL database to terminate its sleeping process –  AgentCool Feb 25 at 8:06
    
@rajagenupula What about SIGINT and SIGHUP? –  Blacklight Shining Feb 26 at 2:54
1  
Programs should be sent signals they can actually handle, in order to give them a chance to clean up. I don't know anything about MySQL, but this seems especially true for database-related processes. If SIGKILL is the only way to get a program to exit, uninstall it and find an alternative. –  Blacklight Shining Feb 27 at 2:56

Here is an one liner for your purpose,

sed 's/[^0-9]//g' file.txt| xargs | sed 's/ /,/g'

or

sed 's/[+|IdUserroot\-]*//g' file.txt | xargs | sed 's/ /,/g'

Output:

8192,8194,8202,8245,8434,8754,8761,8762,8764,8771

Explanation

man sed

s/regexp/replacement/
          Attempt  to  match  regexp  against the pattern space.  If successful, replace that
          portion matched with replacement.  The replacement may contain the special  charac‐
          ter  & to refer to that portion of the pattern space which matched, and the special
          escapes \1 through \9 to refer to the corresponding matching sub-expressions in the
          regexp.

g     Copy/append hold space to pattern space.

Regular expression[Wikipedia]

[^ ]    Matches a single character that is not contained within the brackets.
For example, [^abc] matches any character other than "a", "b", or "c". [^0-9]
matches any single character that is not a number from "0" to "9".

Hence using sed I have replaced everything with space. Next xargs put them in a line separated by space,

$ sed 's/[^0-9]//g' file.txt| xargs
8192 8194 8202 8245 8434 8754 8761 8762 8764 8771

At last step I have replaced all the spaces with , using sed

share|improve this answer
    
Thanks for answering +1 , I like to have some explanation because in future it perhaps helps someone to understood what this command can do. :) –  AgentCool Feb 24 at 18:11
    
@rajagenupula take a look at the edit. Plz let me know if it need any further explanation :) –  souravc Feb 24 at 18:35
    
If i am checking with more number of fields ( they have sensitive information) , its not helping. but with given information you are right. I am not saying that this is wrong but I am saying that its not much accurate for exact extract. Thank you again for answering. –  AgentCool Feb 25 at 9:21

where the text is in an input file in the same directory, and result is given in output file.

cat ./input | sed -e 's/+------+------+//g' | sed -e 's/| Id   | User |//g' | sed -e 's/ | root |//g' | tr -d "\n" | sed -e 's/| /,/g' | sed -e 's/ ,/,/g' | sed -e 's/ ,/,/g' > output

When I did it your example I got:

+------+------,8192,8194,8202,8245,8434,8754,8761,8762,8764,8771

That may because the top line of your example was missing a + - there may be a few other errors...

Then I think you just need to add sudo /mysql_rms/bin/mysqladmin -S /mysql_rms/var/mysql_rms.sock -p kill at the beginning


Or, in what is hopefully a slight improvement on what Radu suggested (also based on the above input):

cat ./input | sed 's/[+-]*//g' | sed 's/ | root |//g' | tr -d "\n" | sed 's/| /,/g' | sed 's/ ,/,/g' | sed 's/ ,/,/g' | sed 's/^,//' | sed 's/,[0-9]* $/\n/' | sed 's/Id//g' | sed 's/,User,,//g' | sed 's/ //g' > output

Then, based on you Q&A here, you should be able to run:

sudo /mysql_rms/bin/mysqladmin -S /mysql_rms/var/mysql_rms.sock -p kill $(cat ./output)

although you may want to check whether it is the right command with this first:

echo "sudo /mysql_rms/bin/mysqladmin -S /mysql_rms/var/mysql_rms.sock -p kill $(cat ./output)"
share|improve this answer
1  
Better to use sed -e 's/[+-]*//g' instead of sed -e 's/+------+------+//g'. –  Radu Rădeanu Feb 24 at 17:07
    
how about fixing that small unwanted thing ? thanks for answering and effort :) –  AgentCool Feb 24 at 18:13
    
@RaduRădeanu - Better? - I would spend more time on learning this sort of thing, but I need more of a certain entity called 'time' –  Wilf Feb 24 at 19:03
    
And cat is not necessary. Look at the man sed; sed can take as argument an input file (and this is normal, since sed is an stream editor). –  Radu Rădeanu Feb 24 at 19:03
    
@Wilf But you have a lot of time, and you spend it on askubuntu like me an others :) –  Radu Rădeanu Feb 24 at 19:07

I think this is what you needed. Some other answers failed to remove the last part.

sed -n 's/.*\([0-9]\{4\}\).*/\1/p' file | sed ':a;N;$!ba;s/\n/,/g; s/\(.*\),\(.*\)$/\1/g'

A small example,

$ (echo '| Id   | User |'; echo '| 8192 | root |'; echo '| 8194 | root |'; echo '| 8771 | root |') | sed -n 's+.*\([0-9]\{4\}\).*+\1+p' | sed ':a;N;$!ba;s/\n/,/g; s/\(.*\),\(.*\)$/\1/g'
8192,8194
share|improve this answer

protected by AgentCool Feb 26 at 5:07

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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