Shell regex: Retrieve prices

Question

Given the following price list, I am trying to figure out how to normalize / extract only numbers.

INPUT          DESIRED_OUTPUT

CA$1399.00     1399.00
$1399.11   1399.11
$1,399.22<     1399.22
Z$1 399.33     1399.33
$1399.44#      1399.44
C$ 1399.55     1399.55
1,399.66       1399.66
1399.77        1399.77
,1399.88       1399.88
25 1399.88     1399.88
399.99          399.99
88.88 99.99      99.99 (if >2 matches on one line, only the last one matters)
.1399.88         DO NOT MATCH (not a price; too many ".")
666.000          DO NOT MATCH (not a price: too many 0's)

I think it's a good idea to start with the fact that everyone has one thing in common:

• Prices always contain .NNbut never contain.NNN

Upon further inspection, other rules become apparent:

• .NNmust be preceded by one or more digits.
• NNN.NNmay precede either ,, or simply digit, but nothing more.
• All subsequent .NNand previous ones *N.NNmark the end of the match.
• , , 1,399.66 (1399.66), , , . 1, 399.66, , 1399.66: 399.66.

sed, grep awk . ?

, , regex sed:

^\d+(,\d{1,2})?$

EDIT: , , .

Answer 1

script:

#/bin/sh
grep -v '\.\d\+\.' | # get rid of lines with multiple dots within the same number
grep -v '\.\d\d\d\+' | # get rid of lines with more than 2 digits after .
sed -e 's/\(.*\.[0-9][0-9]\).*$/\1/' | # remove anything after last .NN
sed -e 's/^.* \([0-9][0-9][0-9][0-9]\)\./\1./' | # "* NNNN." => "NNNN."
sed -e 's/^.* \([0-9][0-9]\)\./\1./' | # "* NN." => "NN."
sed -e 's/^.* \([0-9]\)\./\1./' | # "* N." => "N."
sed -e 's/^\(.*\)[ ,]\(\([0-9]\)\{3,\}\)\./\1\2./g' | # "*,NNN." or "* NNN." => "*NNN."
sed -e 's/^\(.*\)[ ,]\(\([0-9]\)\{6,\}\)\./\1\2./g' | # "*,NNNNNN." or "* NNNNNN." => "*NNNNNN."
sed -e 's/^\(.*\)[ ,]\(\([0-9]\)\{9,\}\)\./\1\2./g' | # "*,NNNNNNNNN." or "* NNNNNNNNN." => "*NNNNNNNNN."
grep -o '\d\+\.\d\d' # print only the price

, , 3 , 9 .. , , 3.; -)

extract_prices, (chmod +x extract_prices) : ./extract_prices < my_list.txt

OS X, :

CA$1399.00
$1399.11
$1,399.22<
Z$1 399.33
Z$12 777 666.34   # <-- additonal monster price
$1399.44#
C$ 1399.55
1,399.66
1399.77
,1399.88
25 1399.88
399.99
88.88 99.99
.1399.88
666.000

:

1399.00
1399.11
1399.22
1399.33
12777666.34
1399.44
1399.55
1399.66
1399.77
1399.88
1399.88
399.99
99.99

Answer 2

awk, , , , . sed script №3, , .

sed -e 's/  / x /g; :a; s/\(\$[1-9][0-9]*\) /\1/; ta' | awk -F '[^0-9.]' -v p='[0-9]+\\.[0-9][0-9]' '$0 ~ p { gsub(/,/, ""); for (i=NF; i>0; i--) if ($i ~ "^" p "$") { print $i; next } }'

:

1) sed script ; , ..
2) sed script , $1 [] [] 1000.00 $11000.00.
3) /... , gsub awk script sed script

, № 3, , .

sed -e ':a; s/\(\$[1-9][0-9]*\) \([0-9][0-9][0-9][ .]\)/\1\2/; ta; :b; s/\([1-9][0-9]*\),\([0-9][0-9][0-9][,.]\)/\1\2/; tb;' | awk -F '[^0-9.]' -v p='[0-9]+\\.[0-9][0-9]' '$0 ~ p { for (i=NF; i>0; i--) if ($i ~ "^" p "$") { print $i; next } }'

, "p" script.

sed -e ':a; s/\(\$[1-9][0-9]*\) \([0-9][0-9][0-9][ .]\)/\1\2/; ta; :b; s/\([1-9][0-9]*\),\([0-9][0-9][0-9][,.]\)/\1\2/; tb;' | awk -F '[^0-9.]' '{ for (i=NF; i>0; i--) if ($i ~ /^[0-9]+\.[0-9][0-9]$/) { print $i; next } }'

, sed, , , .

sed -e ':a; /\$[1-9][0-9]\?[0-9]\?\( [0-9][0-9][0-9]\)\+\.[0-9][0-9]/ s/\(\$[1-9][0-9]*\) \([0-9][0-9][0-9][ .]\)/\1\2/; ta; :b; /[1-9][0-9]\?[0-9]\?\(,[0-9][0-9][0-9]\)\+\.[0-9][0-9]/ s/\([1-9][0-9]*\),\([0-9][0-9][0-9][,.]\)/\1\2/; tb;' | awk -F '[^0-9.]' '{ for (i=NF; i>0; i--) if ($i ~ /^[0-9]+\.[0-9][0-9]$/) { print $i; next } }'

Answer 3

This may work for you (GNU sed):

 sed -r '/\n/!s/([^0-9]*\b(([0-9])[ ,]([0-9]{3})|([0-9]+))(\.[0-9]{2})\b)+/\n\3\4\5\6\n/;/^[0-9]+\.[0-9]{2}\b/P;D' file

This works with the data provided, but some of it is a bit sketchy.