1

I have HTML tags in a column, which are used to define multi-language content on a webserver.

I need to do a query to extract several data from the database (MySQL v5.5) directly. Using stored functions are allowed. Note, I can not change the database structure, I have to use this one.

The step where I stucked is, to extract the language string depending on column named lang from column category.

Category column has strings like the following:

'/<span lang="hu" class="multilang">Hungarian Level 1</span><span lang="en" class="multilang">English Level ONE</span>/<span lang="hu" class="multilang">Hungarian Level 2</span><span lang="en" class="multilang">English Level TWO</span>/Last level'

Not all rows has HTML tags, those are simply Hungarian texts. We don't need to do anything in this case. Depth of levels are unknown, can be several level deep.

If lang column begins with 'hu', then we need to extract only those texts within <span lang="hu" class="multilang"> tags, same for English <span lang="en" class="multilang">.

I found ExtractValue function, which seems to be helpful, but doesn't solve the problem.

SET @xml = '/<span lang="hu" class="multilang">Hungarian Level 1</span><span lang="en" class="multilang">English Level ONE</span>/<span lang="hu" class="multilang">Hungarian Level 2</span><span lang="en" class="multilang">English Level TWO</span>/Last level';
SET @result1 = ExtractValue(@xml, '//span[1]');
SET @result2 = ExtractValue(@xml, '/span[2]');

SELECT @result1;
SELECT @result2;

I have a stored function, like this:

CREATE DEFINER=`root`@`%` FUNCTION `decode_path`(
    `path_input` MEDIUMTEXT
,
    `lang` VARCHAR(50)
)
RETURNS mediumtext CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE cnt INT UNSIGNED DEFAULT 2;
    DECLARE cat_id INT UNSIGNED;
    DECLARE cat_name VARCHAR(200) DEFAULT '';
    DECLARE decoded_path MEDIUMTEXT;
    SET decoded_path = '';

    WHILE cnt < LENGTH(path_input) - LENGTH(REPLACE(path_input, '/', '')) + 2 DO
      SET cat_id = SUBSTRING_INDEX(SUBSTRING_INDEX(path_input,'/',cnt), '/', -1);
      SELECT `name` INTO cat_name FROM mdl_course_categories as c WHERE id = cat_id;
      SET decoded_path := CONCAT(decoded_path, '/', cat_name);
      SET cnt := cnt + 1;
    END WHILE;

    SET @path := decoded_path;
        IF(lang REGEXP 'hu%' ) THEN  
        -- SET decoded_path := REPLACE(decoded_path,'<span lang="en" class="multilang">','');
        SET @result := ExtractValue(@path, '/span[1]');
        ELSEIF (lang REGEXP 'en%' ) THEN  
        -- SET decoded_path := REPLACE(decoded_path,'<span lang="hu" class="multilang">','');
            SET @result := ExtractValue(@path, '/span[2]');
        END IF;

    RETURN @result;
  END

How to parse the multi-language text to display only in language set in lang column?

  • SQL is not a convenient language for string manipulation. Use your application language. – Rick James May 15 '18 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.