Re: SELECT question - Mailing list pgsql-general

Brent Wood wrote:
>
> On Tue, 4 Nov 2003, Alex wrote:
>
>> Hi,
>>
>> I have a bit string , 7 bits, every bit representing a day of the week.
>> e.g. 1110011.
>> Is there and easy way where I can translate/format that string in a query.
>> I want to give the string back with a '-' for every 0 and the first char
>> of the Day for every '1'.
>> example 1100111 = SM--TFS.
>>
>
> You could write a Postgres function to do this, depending on your
> programming skills, but you did ask for a query based solution.

See attachment


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #
--
-- This one is for text arguments
--
drop function bitstring2days(text);
create function bitstring2days(text) returns text
as '
    set result ""
    foreach bit [split $1 {}] day {S M T W T F S} {
        if {"$bit" == "1"} {
            append result $day
        } else {
            append result "-"
        }
    }
    return $result
' language pltcl;

--
-- This one for if your actual days bits are in an integer
--
drop function bitint2days(integer);
create function bitint2days(integer) returns text
as '
    set result ""
    binary scan [binary format c $1] B8 bits
    foreach bit [split [string range $bits 1 end] {}] day {S M T W T F S} {
        if {"$bit" == "1"} {
            append result $day
        } else {
            append result "-"
        }
    }
    return $result
' language pltcl;

select bitstring2days('1100111');
select bitint2days(103);