Page History

...

recs.call_number,

-- get LC class

CASE

WHEN recs.call_number ILIKE '%film%'

...

THEN ' - '

WHEN recs.call_number ILIKE '%thesis%' THEN SUBSTRING (recs.call_number, '\s[A-Za-z]{1,3}')

ELSE SUBSTRING (recs.call_number,'[A-Za-z]{1,3}') END AS lc_class,

-- get LC class number

CASE WHEN recs.call_number ILIKE '%film%' THEN NULL

...

(TRIM (trailing '.' FROM

CASE

WHEN SUBSTRING (recs.call_number,1,1) in ('1','2') THEN NULL

WHEN SUBSTRING (recs.call_number,2,1) = ' ' THEN NULL

ELSE SUBSTRING (recs.call_number,'\d{1,4}\.{0,}\d{0,}')

END))

END AS lc_class_number,

...

TRIM (CASE WHEN SUBSTRING (recs.call_number,1,1) IN ('1','2')

THEN SUBSTRING (recs.call_number,1,4)

ELSE SUBSTRING (recs.call_number, '\s\d{4}\s{0,1}')

END) AS internal_year,

-- get first cutter

CASE WHEN recs.library_name NOT LIKE 'Law%'

THEN SUBSTRING (recs.call_number, '\d\s{0,1}([A-Z]\d{1,})')

ELSE SUBSTRING (recs.call_number, '(\.[A-Z]\d{1,}[a-z]{0,1})')

END AS first_cutter,

-- get second cutter

...

-- then order by first cutter, then second cutter, then the whole call number in case the cutters re blank

first_cutter,

second_cutter,

...