The Problem

I came across an incredibly trivial issue at work the other day that left made me stop and think for a second - How do you count occurences of a string in SQL?

The actual issue I was trying to solve was a particular column had multiple values within it, seperated by a semi colon. I needed to extract these and count how many values there were. It wes a very straightforward import that was coming into SQL - I could have passed it through into another pipeline to process it and calculate the character count in Python, but that was a lot of needless steps to get a single column.

Example

To demonstrate the solution I will use an example table called assets_import (creative name, I know!). It looks as follows

Appreciating it is quite a niche use case - Lets say you need to add a column to the end of this called “count of tag” - How would you solve it?

Solution

The solution is, thankfully, remarkably simple. I will be using postgres, but adapt the inbuilt function names to the needs of your RDBMS:

1
2
3
4
5
6

SELECT
      id
    , asset_title
    , asset_tags
    , LENGTH(asset_tags) - LENGTH(REPLACE(asset_tags, ';','')) + 1 AS asset_tag_count
FROM assets_import

Which outputs:

So how does it work?

1. First we assert the length of the value using LENGTH(asset_tags)
2. Second we take the length of the value, but with the semi colons removed: LENGTH(REPLACE(asset_tags, ';','')) and subtract it from the original length
3. Add one, since we don’t have a trailing ‘;’ lets assume the following scenario:
   • one - Has no semi colon but one tag
   • one;two - Has one semi colon, but two tags
   • one;two;three - Has two semi colons, three tags

Edge cases

The data set I was working with always had atleast one tag, but its worth mentioning you may encounter a scenario where there is an empty string or null value and want present 0 tags.

I’ve added a couple more rows to the database to show how this may look:

In this scenario we can use a CASE statement:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    id
  , asset_title
  , asset_tags
  , CASE
      WHEN asset_tags IS NULL THEN 0
      WHEN LENGTH(asset_tags) = 0 THEN 0
      ELSE LENGTH(asset_tags) - LENGTH(REPLACE(asset_tags, ';','')) + 1 
    END AS asset_tag_count
FROM assets_import

We now get the following output:

Conclusion

Hopefully you found this useful, I found this incredibly simple problem quite novel in its solution and in the spirit of sharing thought others may be interested too!

I have included a full version of the final code below if you want to give it a try

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

--DROP TABLE IF EXISTS assets_import;

CREATE TABLE assets_import (
  id INT,
  asset_title TEXT,
  asset_tags TEXT
);

INSERT INTO assets_import (id, asset_title, asset_tags) VALUE
(1, 'Leather Jacket', 'leather;jacket;casual;black'),
(2, 'Jeans', 'denim;pants;slim-fit;casual;blue'),
(3, 'Suit Jacket', 'business;black;jacket;cotton'),
(4, 'Key Chain', 'keychain'),
(5, 'Coat Hanger', ''),
(6, 'Clothes Peg', NULL);

SELECT
    id
  , asset_title
  , asset_tags
  , CASE
      WHEN asset_tags IS NULL THEN 0
      WHEN LENGTH(asset_tags) = 0 THEN 0
      ELSE LENGTH(asset_tags) - LENGTH(REPLACE(asset_tags, ';','')) + 1 
    END AS asset_tag_count
FROM assets_import;