T-SQL field parsing

Question

There is a field in a 3rd party database that I need to group on for a report I'm writing. The field can contain a few different types of data. First it could contain a 3 digit number. I need to break these out into groups such as 101 to 200 and 201 to 300. In addition to this the field could also be prefaced with a particular letter such a M or K then a few numbers. It is defined as VARCHAR(8) and any help in how I could handle both cases where it may start with a particular letter or fall within a numeric range would be appreciated. If I could write it as a case statement and return a department based either on the numeric value or the first letter that would be the best so I can group in my report.

Thanks, Steven

Answer 1

If I could write it as a case statement and return a department based either on the numeric value or the first letter that would be the best so I can group in my report.

case when substring( field, 1, 1 ) = 'M' then ...
when substring( field, 1, 1 ) = 'K" then ...
else floor( (cast( field as int) - 1 ) / 100) end 


   select ....
   group by 
    case when substring( field, 1, 1 ) = 'M' then ...
    when substring( field, 1, 1 ) = 'K" then ...
    else floor( (cast( field as int) - 1 ) / 100) end

Matt Hamilton asks,

Any reason why you've opted to use substring(field, 1, 1) rather than simply left(field, 1)? I notice that @jms did it too, in the other answer.

I know substring is specified in ANSI-92; I don't know that left is. And anyway, left isn't a primitive, as it can be written in terms of substring, so using substring seems a little cleaner.

Answer 2

select
CASE  (CASE WHEN substring(field,1,1) between 0 and 9 then 'N' Else 'C' END)
WHEN 'N' THEN
    CASE field
        WHEN ... THEN ...
        WHEN ... THEN ...
    END
WHEN 'C' THEN
    CASE field
        WHEN ... THEN ...
        WHEN ... THEN ...
    END
END