Skip to main content

Counting characters in MySQL

This blog post might be outdated!
This blog post was published more than one year ago and might be outdated!
· One min read
Stephan Hochdörfer
Stephan Hochdörfer
Head of IT Business Operations

For some data integrity checks, I needed to know if all SKUs stored in a MySQL database table have been properly imported. That implied making sure each SKU contained a string with 2 underscores as a separator. Knowing that PHP has functions like substr_count() or count_chars(), I was surprised that nothing similar exists in MySQL.

Thankfully, I came across this neat little trick to achieve the same result:

SELECT entity_id, (LENGTH(sku)-LENGTH(REPLACE(sku, '_', ''))) AS char_count WHERE char_count = 2 FROM catalog_product_entity;