Re: SELECT question

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)