SELECT question

[Alex]

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



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Brent Wood]

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)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alvaro Herrera]

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!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jan Wieck]

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. #
#================================================= = Ja******@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);
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster