0

I am aware that in ClickHouse we must use the equality operator with its ON conditionals. Any suggestion to solve cases where we have to use a LIKE operator, similarly what is done with MySQL?

Something like:

SELECT *
  FROM TABLE a
  JOIN TABLE b ON b.column LIKE CONCAT('%', a.column ,'%')

Just for testing, I have tried to use also a second conditional similar to the first example of the JOIN documentation:

SELECT course_id, usage_key , display_name  
  FROM video_events  LEFT JOIN video_info 
  ON video_events.course_id = video_info.course_key 
  AND startsWith(video_info.display_name, 'Choro');

but I get the error:

Code: 403. DB::Exception: Received from localhost:9000. DB::Exception: Expected equality or inequality, got 'startsWith(display_name, 'Choro')': While processing startsWith(display_name, 'Choro').

Even though I have a row with the string above:

e19b1e00a6b6 :) select course_id, usage_key , display_name  from video_events  JOIN video_info on video_events.course_id = video_info.course_key;

┌─────course_id───────────────────────────┬─usage_key─────────────────────────────────────────────────────────────────────────────────┬─display_name───────────────┐    
│ course-v1:Introduction+CS001+2021autumn │ block-v1:Introduction+CS001+2021autumn+type@chapter+block@379e3fbc409744d9bdaffa309dd6e20d    │ Section 1                  │
│ course-v1:Introduction+CS001+2021autumn │ block-v1:Introduction+CS001+2021autumn+type@sequential+block@e09c8c6e1ec746d1a31b634438f42071 │ Videos             │
│ course-v1:Introduction+CS001+2021autumn │ block-v1:Introduction+CS001+2021autumn+type@vertical+block@ac5ab25121044b1da4e9658e2d876a4b   │ Classical          │
│ course-v1:Introduction+CS001+2021autumn │ block-v1:Introduction+CS001+2021autumn+type@video+block@ec9ba45b09e74ee98858ee40e28e31e9      │ Debussy            │
│ course-v1:Introduction+CS001+2021autumn │ block-v1:Introduction+CS001+2021autumn+type@vertical+block@7799a7acf46d46e0a3b1b1298d6e542b   │ Brasileira         │
│ course-v1:Introduction+CS001+2021autumn │ block-v1:Introduction+CS001+2021autumn+type@video+block@7b91e95676eb472d8f528d701c5fe929      │ Choro              │
│ course-v1:Introduction+CS001+2021autumn │ block-v1:Introduction+CS001+2021autumn+type@sequential+block@a1586de2d0ca4b31ae24cf3146e09c96 │ Forum              │
└─────────────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────────┘

I want to filter in the above string column usage_key of the table video_info an ID (e.g. 7799a7acf46d46e0a3b1b1298d6e542b) from the video_id column of the table video_events.

I also thought about using the match for searching strings, but I still couldn't figure out how to use it with the ON conditional.

everton137
  • 1
  • 3
  • Does this answer your question? [How to search the string in query with case insensitive on Clickhouse database?](https://stackoverflow.com/questions/58728436/how-to-search-the-string-in-query-with-case-insensitive-on-clickhouse-database) – philipxy Sep 28 '21 at 18:19

2 Answers2

0

Try this one:

SELECT * 
FROM (
  SELECT course_id, usage_key , display_name  
  FROM video_events  LEFT JOIN video_info 
    ON video_events.course_id = video_info.course_key 
  )
WHERE startsWith(display_name, 'Choro');
vladimir
  • 13,428
  • 2
  • 44
  • 70
  • This works, @vladimir! Thanks. Now I have to figure out how could I make the regular expression, 'Choro', in this case, be any value of the table `video_event.video_id`. – everton137 Sep 28 '21 at 17:45
  • I could match a particular video ID using the match funcion I mentioned in the original post: `SELECT *　FROM (SELECT course_id, usage_key , display_name FROM video_events INNER JOIN video_info ON video_events.course_id = video_info.course_key)　WHERE match(usage_key, '7b91e95676eb472d8f528d701c5fe929');` – everton137 Sep 28 '21 at 17:48
  • @everton137 I would consider using *LIKE* or *ILIKE* operator - *select .. where display_name like '%Choro%'* – vladimir Sep 28 '21 at 18:28
0

I have found a way to solve what I wanted by using the replaceRegexpOne function to filter the column that had the IDs of the other table so that I could use the equality when joining both tables.

So I got a query like this:

SELECT course_id, user_id, replaceRegexpOne(usage_key, '.*(video\+block@)(.*)', '\\2') AS video_info_id, display_name 
  FROM video_info 
  INNER JOIN video_events 
  ON video_info_id = video_id ;

which return this table with the columns course_id, user_id, video_id and video_display_name:

course-v1:Introduction+CS001+2021autumn 11  ec9ba45b09e74ee98858ee40e28e31e9    Debussy
course-v1:Introduction+CS001+2021autumn 11  ec9ba45b09e74ee98858ee40e28e31e9    Debussy
course-v1:Introduction+CS001+2021autumn 10  7b91e95676eb472d8f528d701c5fe929    Choro
course-v1:Introduction+CS001+2021autumn 10  7b91e95676eb472d8f528d701c5fe929    Choro
everton137
  • 1
  • 3