1. You can check out the full PostgreSQL documentation for substring
here.
SUBSTRING (string FROM x FOR and)
This will return "and" characters from the "x" position of the text "string".
SUBSTRING (' abcdefg' from 2 for 3) = ' bcd'
This is an example of the above. 3 characters from position 2 in 'abcdefg' is 'bcd'.
SUBSTRING (lookup_actions_p.action from 1 for 2) = 'CC'
This is an example of how you could use it in PT4. This expression will be true if the 2 characters from position 1 of lookup_actions_p.action are 'CC'.
2.
char_length(tourney_hand_summary.str_aggressors_p) >= 2
This says the length of the aggressors string is greater than or equal to 2. I.e. there are 2 or more characters in the string. If you're going to compare the first 2 characters of a string then first you need to make sure that there are at least 2 characters to compare.
char_length(tourney_hand_summary.str_aggressors_t) >= 1
This is the same as the above, but for length 1.
substring(tourney_hand_summary.str_aggressors_t from 1 for 1)::int = substring(tourney_hand_summary.str_aggressors_p from '.$')::int
::int turns a character into a number (int = integer), so this expression tests whether the first character of the turn aggressors string is equal to the last character of the preflop aggressors string. I.e. Whether the preflop aggressor bet the turn.