[Koha] Extracting Frequency of a Serial From metadata

[RAGHAV ARORA]

Hello everyone,

The SQL query to get the frequency for different serials was given on the
KOHA SQL Library as :
                       SELECT
                        IF
                        (   LOCATE('<datafield tag="310"',
biblio_metadata.metadata) = 0
                                OR
                            LOCATE('<subfield code="a">',
biblio_metadata.metadata, LOCATE('<datafield tag="310"',
biblio_metadata.metadata)) = 0
                                OR
                            LOCATE('<subfield code="a">',
biblio_metadata.metadata, LOCATE('<datafield tag="310"',
biblio_metadata.metadata))
                                > LOCATE('</datafield>',
biblio_metadata.metadata, LOCATE('<datafield tag="310"',
biblio_metadata.metadata)),
                        '',
                        SUBSTRING( biblio_metadata.metadata,
                            LOCATE('<subfield code="a">',
biblio_metadata.metadata, LOCATE('<datafield tag="310"',
biblio_metadata.metadata)) + 19,
                            LOCATE('</subfield>', biblio_metadata.metadata,
LOCATE('<subfield code="a">', biblio_metadata.metadata,
                                LOCATE('<datafield tag="310"',
biblio_metadata.metadata)) + 19)
                                -
                            (LOCATE('<subfield
code="a">',biblio_metadata.metadata, LOCATE('<datafield tag="310"',
biblio_metadata.metadata)) + 19)
                            )
                        )
                    AS FREQUENCY

When I run this query, it gives the output like 54/yr for some journals,
which is correct, but for some, it returns something like 'M', '0', 'FN',
'SM', '#N/A', etc.
Can someone please help me understand what should be the correct SQL query?

Thanks in advance
Regards
*Raghav Arora*
Sophomore, M.Sc (Hons) Chemistry BE Electrical and Electronics Engineering
Contact : (+91) 9897597761, 8474975691
Personal Email <raghavarora999 at yahoo.in> | University Email
<f20171016 at pilani.bits-pilani.ac.in>
LinkedIn <https://www.linkedin.com/in/raghav-arora-9820a648/> |  GitHub
<https://github.com/RAraghavarora/>

▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄
*Birla Institute of Technology and Science, Pilani*
Pilani campus,
Rajasthan-333031