Ask Ubuntu is a question and answer site for Ubuntu users and developers. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


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 '14 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. – Raja Feb 24 '14 at 15:56
Is this still needed due to Kill Sleeping Process in MySQL ? :D – Wilf Feb 24 '14 at 16:02
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. – Raja Feb 24 '14 at 16:04
almost All answers are excellent , which one I have to accept thats big deal. – Raja Feb 24 '14 at 19:04
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. – Raja Feb 24 '14 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 '14 at 18:58

The most shortest way I can find:

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

Result is:


[^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 – Raja Feb 24 '14 at 18:12
Shouldn't you be sending SIGTERMs first? – Blacklight Shining Feb 24 '14 at 20:53
I cannot send SIGTERM to a MySQL database to terminate its sleeping process – Raja Feb 25 '14 at 8:06
@rajagenupula What about SIGINT and SIGHUP? – Blacklight Shining Feb 26 '14 at 2:54
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 '14 at 2:56

Here is an one liner for your purpose,

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


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




man sed

          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

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. :) – Raja Feb 24 '14 at 18:11
@rajagenupula take a look at the edit. Plz let me know if it need any further explanation :) – souravc Feb 24 '14 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. – Raja Feb 25 '14 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:


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
Better to use sed -e 's/[+-]*//g' instead of sed -e 's/+------+------+//g'. – Radu Rădeanu Feb 24 '14 at 17:07
how about fixing that small unwanted thing ? thanks for answering and effort :) – Raja Feb 24 '14 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 '14 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 '14 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 '14 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'
share|improve this answer

protected by Raja Feb 26 '14 at 5:07

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

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.