/commands/awk/awk-filter-rows-by-column-value
awk '$3 > 100': filter rows by a column value
- tool
- awk
- versions
- gawk 5.x, mawk 1.3, BSD awk
- os
- linux, macos, windows
awk '$3 > 100' file prints the lines whose third field is greater than 100. A pattern with no action prints the whole line, so the condition alone is the filter. Use -F, for CSV files (awk -F, '$3 > 100' data.csv), == for an exact text match ($2 == "web"), ~ for a regex ($2 ~ /^db/) and && or || to combine conditions. Fields are numbered from $1; $0 is the whole line and $NF the last field.
Example
cat > hosts.csv <<'EOF'
name,role,cpu
web1,web,85
db1,db,120
web2,web,140
cache1,cache,9
EOF
awk -F, 'NR > 1 && $3 > 100' hosts.csv
echo ===
awk -F, '$2 == "web"' hosts.csv
echo ===
awk -F, 'NR == 1 || ($2 ~ /^(db|cache)$/ && $3 < 150)' hosts.csv
echo ===
awk -F, -v min=100 'NR > 1 && $3 >= min { print $1 }' hosts.csvOutput
db1,db,120 web2,web,140 === web1,web,85 web2,web,140 === name,role,cpu db1,db,120 cache1,cache,9 === db1 web2
NR > 1 skips the header. The second command matches the role exactly. The third keeps the header and the rows whose role matches a regex and whose CPU is under 150. The last one takes the threshold from -v and prints only the name column.
Conditions
| Pattern | Keeps lines where |
|---|---|
| $3 > 100 | Field 3 is greater than 100 (numeric comparison) |
| $2 == "web" | Field 2 is exactly web |
| $2 != "web" | Field 2 is anything else |
| $2 ~ /^db/ | Field 2 matches the regex ^db |
| $2 !~ /test/ | Field 2 does not contain test |
| $3 > 100 && $2 == "db" | Both conditions |
| /ERROR/ | The whole line matches ERROR (like grep) |
| NF > 3 | The line has more than three fields |
| length($0) > 80 | The line is longer than 80 characters |
| NR >= 10 && NR <= 20 | Lines 10 to 20 |
Numbers compared as text
awk decides per comparison whether to compare numbers or strings. Field values that look like numbers are compared numerically, but a constant in quotes is a string, and then the comparison is done character by character:
printf '%s\n' 9 10 100 > n.txt
awk '$1 > 50' n.txt
echo ===
awk '$1 > "50"' n.txtOutput
100 === 9
With 50 the result is 100 only. With "50" the comparison is textual, and 9 is greater than 50 because the character 9 sorts after 5. Keep numeric thresholds unquoted, and force a number with $1 + 0 when a field may carry spaces or a unit.
Pitfalls
=is assignment.awk '$2 = "web"'sets field 2 on every line and prints them all. Use==.- Wrong separator. With the default separator (runs of blanks), a CSV line is one field and every
$3is empty. Add-F,. - Windows line endings. The last field ends with
\r, so$NF == "web"never matches. Strip it withsub(/\r$/, "")at the start of the program or withtr -d '\r'first. - Quoting. On Linux and macOS put the program in single quotes so the shell does not expand
$3.
To total the filtered values instead of printing them, combine the filter with a sum: awk -F, '$2 == "web" { s += $3 } END { print s }' hosts.csv.
FAQ
How do I keep the header line while filtering?
Add NR == 1 as an alternative, awk -F, 'NR == 1 || $3 > 100' data.csv. NR is the record number, so the first line is always printed and the condition applies to the rest.
How do I pass the threshold from a shell variable?
Use -v, as in awk -v min="$min" '$3 > min' file. Do not paste the shell variable into the awk program: quoting breaks easily and the value becomes code.
Why does awk '$2 == "web "' not match?
With -F, fields keep their spaces, so a CSV written as ada, web has a field ' web' with a leading space. Compare after trimming, or set the separator to a regex such as -F' *, *'.