Getting number from place value in sql

1
votes

I have a form set up that has Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday. When populated into the SQL database it starts off with 7 0s, each 0 representing the day of the week and when a day of the week is selected a 1 in the correct place. For instance if Monday is selected the value would be 1000000, if Tuesday 010000, Wednesday 001000 etc. Users have the option to select multiple days so it could be 101000 for Monday and Wednesday or 1111111 for all days. What would be the best route to convert this into a sql query to say if the 1 is in the hundred thousands place that equals Monday and if the 1 is in 101000 the value is Monday and Wednesday?

Let me know, thanks!

2
sqlsql-servertsqlnumbersdotnetnuke
I don't understand this question. Are you asking about data formats in the database? What does the underlying table look like? Can you change it? – Gordon Linoff
It's almost like a bitmask, but worse. – JNevill
What does this have to do with dotnetnuke? – VDWWD
Why you don't just use seven bit columns, one for each day of the week? – Alberto Martinez

2 Answers

2
votes

If 2012+ You can use concat(). Also included STUFF() for a "cleaner" string.

Example

Declare @YourTable table (SomeCol varchar(25))
Insert Into @YourTable values
('1000000'),
('1100000'),
('0100000'),
('1111111')

Select *
      ,NewCol = stuff(
                concat(
                ', '+IIF(substring(SomeCol,1,1)='1','Monday'   ,null)
               ,', '+IIF(substring(SomeCol,2,1)='1','Tuesday'  ,null)
               ,', '+IIF(substring(SomeCol,3,1)='1','Wednesday',null)
               ,', '+IIF(substring(SomeCol,4,1)='1','Thursday' ,null)
               ,', '+IIF(substring(SomeCol,5,1)='1','Friday'   ,null)
               ,', '+IIF(substring(SomeCol,6,1)='1','Saturday' ,null)
               ,', '+IIF(substring(SomeCol,7,1)='1','Sunday'   ,null)
               ),1,2,'')
 From  @YourTable

Returns

SomeCol   NewCol
1000000   Monday
1100000   Monday, Tuesday
0100000   Tuesday
1111111   Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

EDIT - For 2008

Declare @YourTable table (SomeCol varchar(25))
Insert Into @YourTable values
('1000000'),
('1100000'),
('0100000'),
('1111111')

Select *
      ,NewCol = stuff(
                case when substring(SomeCol,1,1)='1' then ', Monday'    else '' end
               +case when substring(SomeCol,2,1)='1' then ', Tuesday'   else '' end
               +case when substring(SomeCol,3,1)='1' then ', Wednesday' else '' end
               +case when substring(SomeCol,4,1)='1' then ', Thursday'  else '' end
               +case when substring(SomeCol,5,1)='1' then ', Friday'    else '' end
               +case when substring(SomeCol,6,1)='1' then ', Saturday'  else '' end
               +case when substring(SomeCol,7,1)='1' then ', Sunday'    else '' end
               ,1,2,'')
 From  @YourTable
1
votes

Sounds quite odd... but perhaps you are looking for something like this:

declare @var varchar(64) = '1010111'

select
    isnull(case when left(@var,1) = 1 then 'Monday' end,'')
    + ' ' +
    isnull(case when substring(@var,2,1) = 1 then 'Tuesday' end,'')
    + ' ' +
    isnull(case when substring(@var,3,1) = 1 then 'Wednesday' end,'')
    + ' ' +
    isnull(case when substring(@var,4,1) = 1 then 'Thursday' end,'')
    + ' ' +
    isnull(case when substring(@var,5,1) = 1 then 'Friday' end,'')
    + ' ' +
    isnull(case when substring(@var,6,1) = 1 then 'Saturday' end,'')
    + ' ' +
    isnull(case when right(@var,1) = 1 then 'Sunday' end,'')