This is probably only of limited usefulness in practice, but it came as a spinoff from a discussion on
#postgresql. The basic problem is, given a string which you already know is an English weekday name, return the day number (Sunday=0):
create function dayno(text) returns integer language sql immutable as $f$ select (( ((ascii(substring($1 from 3)) & 22)*10) # (ascii($1) & 23) )*5 + 3) % 7; $f$;
I came up with several alternative functions, but this one has the advantage of only needing one
substring() call (it makes use of the fact that
ascii() looks only at the first character). It’s also case-insensitive (since it looks at only the low bits of the characters).
select dayno(d) from regexp_split_to_table('Sun,MON,tUe,WeD,tHU,FRi,saT',',') r(d); dayno ------- 0 1 2 3 4 5 6 (7 rows)