Post reply

How to get a count of occurences of a string within multiple substrings

  • February 12, 2009 at 9:04 am

    #127679

    I have a 168 character varchar field which represents a week broken down into hour long segements (24*7). Each hour can either be available ("9") or unavailable ("0"). So a week where the whole of Tuesday was available, but the rest of the week was unavailable would be represented by 24 zeroes, followed by 24 nines, followed by 120 zeroes.

    I am trying to write a query which will tell me how many days in a week have at least some availability. So if any of the 24 character blocks include a 9, then that day would count as having availability. Is there a simple way to do this? Thanks,

    Tom

  • February 12, 2009 at 9:15 am

    #942966

    If you have a Numbers table (some people call it a Tally table), you can query it using something like:

    create table #Numbers (

    Number int primary key);

    insert into #Numbers (Number)

    select top 1000 row_number() over (order by object_id)

    from sys.all_objects;

    declare @String char(168);

    select @String = replicate('0',24) + replicate('9',24) + replicate('0',120)

    ;with Available (Day, Avail) as

    (select (number-1)/24 + 1, substring(@String, number, 1)

    from #Numbers

    where number between 1 and len(@String))

    select distinct Day

    from Available

    where Avail = 9;

    That query would give you the days that have times available.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply