using sed to remove character at the end of numeric string

Question

Here's a sample of my source file:

USC00020958 -7777S 656S 721S 803S 891S 975S 972S 941S 906S 809S 695S 605S USC00021001 378S 389R 448R 522R 628R 736S 774S 742S 674S 562S 454R 376R USC00021026 689R 729R 792R 873R 969R 1060R 1082R 1068R 1015R 903R 775R 682R USC00021050 663S 713S 796S 885S 984S 1079S 1122S 1103S 1036S 904C 754S 651S USC00501466 -77R 8R 168R 408R 593R 723R 733R 660R 529R 273R 54R -7R USW00501492 -43R 18R 132R 308R 510R 666R 687R 605R 466R 239R 45R 12R USW00501497 3Q -1Q 0P 203Q 388Q 539Q 558Q 493Q 362Q 175Q 51Q 27Q USW00501684 -118R 0P 211R 413R 573R 700R 710R 644R 520R 294R 35R -67R USW00501926 184S 223S 303S 418S 544S 651S 661R 617S 519S 353S 227S -7777S 

I'd like to remove C,S,R,P,Q from the end of 1-4 Digits, while preserving negative (-) numbers, and without modifying the first column (USxxxxxxxxx) in this example). I'd also like to change the formatting to have a single space (or comma) between each value. Here's what I tried, but it's not producing the results I was hoping for:

[user@host1 ~]# cat sample.txt | sed -E 's/(\S+)\s+(.[0-9]{1,4})[C,S,R,P,Q]/\1 \2/g' USC00020958 -7777 656S 721 803S 891 975S 972 941S 906 809S 695 605S USC00021001 378 389R 448 522R 628 736S 774 742S 674 562S 454 376R USC00021026 689 729R 792 873R 969 1060R 1082 1068R 1015 903R 775 682R USC00021050 663 713S 796 885S 984 1079S 1122 1103S 1036 904C 754 651S USC00501466 -77 8R 168 408R 593 723R 733 660R 529 273R 54 -7R USW00501492 -43 18R 132 308R 510 666R 687 605R 466 239R 45 12R USW00501497 3 -1Q 0 203Q 388 539Q 558 493Q 362 175Q 51 27Q USW00501684 -118 0P 211 413R 573 700R 710 644R 520 294R 35 -67R USW00501926 184 223S 303 418S 544 651S 661 617S 519 353S 227 -7777S 

Answer 1

A couple things I noticed about your regular expression:

1. When specifying a character class, you do not use commas between letters. You can use dashes to specify ranges, but in this case, instead of [C,S,R,P,Q], it should be [CSRPQ].
2. sed doesn't require that you match the entire line, so you can just build a pattern to match the numbers with the trailing letter, and then just substitute it for the number by itself (dropping the letter).

I took the input file that you provided and tested out the following sed command:

sed -E 's/([0-9]+)[CSRPQ]/\1/g' sample.txt 

This yields the following output:

USC00020958 -7777 656 721 803 891 975 972 941 906 809 695 605 USC00021001 378 389 448 522 628 736 774 742 674 562 454 376 USC00021026 689 729 792 873 969 1060 1082 1068 1015 903 775 682 USC00021050 663 713 796 885 984 1079 1122 1103 1036 904 754 651 USC00501466 -77 8 168 408 593 723 733 660 529 273 54 -7 USW00501492 -43 18 132 308 510 666 687 605 466 239 45 12 USW00501497 3 -1 0 203 388 539 558 493 362 175 51 27 USW00501684 -118 0 211 413 573 700 710 644 520 294 35 -67 USW00501926 184 223 303 418 544 651 661 617 519 353 227 -7777 

The command is matching any number that has a trailing [CSRPQ] and then stripping the letter.

Also, if you want to remove all of the spaces and replace with a comma to turn this into a CSV, sed lets you chain expressions. However, when specifying multiple scripts, you need to put -e in front of them to tell sed that a script is going to follow. From the help:

Usage: sed [OPTION]... {script-only-if-no-other-script} [input-file]... -e script, --expression=script add the script to the commands to be executed 

Here is an example:

sed -E -e 's/([0-9]+)[CSRPQ]/\1/g' -e 's/\s+/,/g' sample.txt 

This says run the first substitution, then run the second substitution, replacing repeating space characters with a single comma. This yields the following output:

USC00020958,-7777,656,721,803,891,975,972,941,906,809,695,605 USC00021001,378,389,448,522,628,736,774,742,674,562,454,376 USC00021026,689,729,792,873,969,1060,1082,1068,1015,903,775,682 USC00021050,663,713,796,885,984,1079,1122,1103,1036,904,754,651 USC00501466,-77,8,168,408,593,723,733,660,529,273,54,-7 USW00501492,-43,18,132,308,510,666,687,605,466,239,45,12 USW00501497,3,-1,0,203,388,539,558,493,362,175,51,27 USW00501684,-118,0,211,413,573,700,710,644,520,294,35,-67 USW00501926,184,223,303,418,544,651,661,617,519,353,227,-7777 

Hope this helps!