23 lines
715 B
Plaintext
23 lines
715 B
Plaintext
create or replace function middle3digits_helper(s) as (
|
|
select if (invalid_character, 'invalid character found',
|
|
if (invalid_n, 'invalid number of digits',
|
|
s[p+1 : p+3])) as result
|
|
from (select
|
|
regexp_matches(s, '[^0-9]') as invalid_character,
|
|
(length(s) < 3 or (length(s) % 2 = 0)) as invalid_n,
|
|
(length(s) - 3) // 2 as p
|
|
)
|
|
);
|
|
|
|
create or replace function middle3digits(number) as (
|
|
middle3digits_helper(replace(trim(number::VARCHAR),'-',''))
|
|
);
|
|
|
|
## Examples
|
|
select middle3digits('xyzzy');
|
|
|
|
select n, middle3digits(n)
|
|
from unnest( [
|
|
123, 12345, 1234567, 987654321, 10001, -10001, -123, -100, 100, -12345,
|
|
1, 2, -1, -10, 2002, -2002, 0 ]) _(n) ;
|