Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT question

SELECT question

From
Alex
Date:
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.

thanks for any suggestions
Alex

Re: SELECT question

From
Richard Huxton
Date:
On Tuesday 04 November 2003 10:54, 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 probably want a small function in plpgsql - see the procedural languages
section of the manual for details. You might want to check the cookbook at
http://techdocs.postgresql.org/ and see if there's similar code you can use
as inspiration.

--
  Richard Huxton
  Archonet Ltd

Re: SELECT question

From
Brent Wood
Date:

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.

An SQL based approach could use a series of SQL's to substring the
1010101 into separate attributes, then update each accordingly & join
them back into a single attribute afterward. A bit more cumbersome but
for those with SQL capabilities but weak on programming this is pretty
straightforward. Wrap the whole lot in a shell script for ease of use & a
one off run. Not elegant but for a one off it should suffice.

As shown below....


Cheers,

  Brent Wood





eg: (off the top of my head- this approach should work OK as a script,
tho you may need to tweak the syntax & fit your attributes into the
commands)


/bin/sh

#select data into new table with day of week as separate attrs
psql -d <db> -c "select into table temp_days
                        attr1,
                        attr2,
                        substring(days_of_week, 1,1) as 'sun',
                        substring(days_of_week, 2,1) as 'mon',
                        ...
                        ;"

# update each day depending on 0 or 1, sun shown as example
psql -d <db> -c "update temp_days
                        set sun 'S' where sun = '1';"

psql -d <db> -c "update temp_days
                        set sun '-' where sun = '0';"

....

# concat all the days back into a single attribute
psql -d <db> -c "select into table new_table
                        attr1,
                        attr2,
                        sun || mon || ....  as days_of_week,
                        ...
                        ;"

#finally drop the old table (once you are happy with the result)

Re: SELECT question

From
Alvaro Herrera
Date:
On Tue, Nov 04, 2003 at 07:54:54PM +0900, Alex wrote:

> 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.

A simple perl function:

sub mybits {
    my $bits = shift;
    my $ret = "";
    foreach my $i (0 .. 6) {
        substr($ret, $i, 1,
                (substr($bits, $i, 1) =~ /1/ ?
                 (qw(S M T W T F S))[$i] : "-"));
    }
    return $ret;
}

$ ./test.pl 1001011
S--W-FS

You can of course use it with plperl.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Hi! I'm a .signature virus!
cp me into your .signature file to help me spread!

Re: SELECT question

From
Jan Wieck
Date:
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);