CODEWITHSUNDEEP
mysqlbase64substringtext-manipulationstring-function
JimRomeFan
JimRomeFan

Reputation: 413

How to extract part of a Base64 encoded string in MySQL?

I have a field in my database which is encoded. After using from_base64 on the field it looks like this:

<string>//<string>//<string>/2017//06//21//<string>//file.txt

There may be an undetermined number of strings at the beginning of the path, however, the date (YYYY//MM//DD) will always have two fields to the right (a string followed by file extension).

I want to sort by this YYYY//MM//DD pattern and get a count for all paths with this date.

So basically I want to do this:

select '<YYYY//MM//DD portion of decoded_path>', count(*) from table group by '<YYYY//MM//DD portion of decoded_path>' order by '<YYYY//MM//DD portion of decoded_path>';

Upvotes: 6

Views: 820

Answers (2)

Towfik Alrazihi
Towfik Alrazihi

Reputation: 546

little crazy but it works

select REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE('<string>//<string>//<string>/2017//06//21//<string>//file.txt',"//","-"),"/",-1),"-<",1),"-","/"), count(*) from `chaissilist` group by REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE('<string>//<string>//<string>/2017//06//21//<string>//file.txt',"//","-"),"/",-1),"-<",1),"-","/") order by REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE('<string>//<string>//<string>/2017//06//21//<string>//file.txt',"//","-"),"/",-1),"-<",1),"-","/");

Upvotes: 1

Steve Chambers
Steve Chambers

Reputation: 39434

Summary

MySQL's SUBSTRING_INDEX comes in useful for doing this by looking for the specified delimiter and counting backwards from the end if a negative count value is specified.

Demo

Rextester demo: http://rextester.com/TCJ65469

SQL

SELECT datepart, 
       COUNT(*) AS occurrences
FROM
(SELECT CONCAT(
     LEFT(SUBSTRING_INDEX(txt, '//', -5), INSTR(SUBSTRING_INDEX(txt, '//', -5), '//') - 1),
     '/',
     LEFT(SUBSTRING_INDEX(txt, '//', -4), INSTR(SUBSTRING_INDEX(txt, '//', -4), '//') - 1),
     '/',
     LEFT(SUBSTRING_INDEX(txt, '//', -3), INSTR(SUBSTRING_INDEX(txt, '//', -3), '//') - 1))
   AS datepart
 FROM tbl) subq
GROUP BY datepart
ORDER BY datepart;

Assumptions

Have assumed for now that the single slash before the year in the example given in the question was a typo and should have been a double slash. (If it turns out this isn't the case I'll update my answer.)

Upvotes: 5

Related Questions