Skip to content

Instantly share code, notes, and snippets.

@ateneva

ateneva/SQL_instr_vs_locate.sql

Last active September 2, 2018 13:20
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ateneva/3c4cfb1d2056cdddbe807b33260a822f to your computer and use it in GitHub Desktop.
Save ateneva/3c4cfb1d2056cdddbe807b33260a822f to your computer and use it in GitHub Desktop.
Download ZIP
What is the difference between instr() and locate() in MySQL?
Raw
SQL_instr_vs_locate.sql
/* MySQL */
select
tags,
instr(tags, 'TED'),
locate('TED', tags),
locate('TED', tags, 5),
instr(tags, 'ted'),
locate('ted', tags),
locate('ted', tags, 5),
instr(tags, 'TED%'),
locate('TED%', tags),
locate('TED%', tags, 5)
from datageeking.ted_talks
/*
tags |instr(tags, 'TED') |locate('TED', tags) |locate('TED', tags, 5) |
----------------------------------------------|-------------------|--------------------|-----------------------|
['children', 'creativity', 'culture', 'dance' |0 |0 |0 |
['alternative energy', 'cars', 'climate chang |0 |0 |0 |
['computers', 'entertainment', 'interface des |0 |0 |0 |
['MacArthur grant', 'activism', 'business', ' |0 |0 |0 |
['Africa', 'Asia', 'Google', 'demo', 'economi |0 |0 |0 |
['business', 'culture', 'entertainment', 'goa |0 |0 |0 |
['Christianity', 'God', 'atheism', 'comedy', |0 |0 |0 |
['architecture', 'collaboration', 'culture', |0 |0 |0 |
['God', 'TED Brain Trust', 'atheism', 'brain' |10 |10 |10 |
['Christianity', 'God', 'culture', 'happiness |0 |0 |0 |
['activism', 'architecture', 'collaboration', |0 |0 |0 |
['TED Prize', 'art', 'culture', 'entertainmen |3 |3 |0 |
['TED Prize', 'collaboration', 'disease', 'eb |3 |3 |0 |
['demo', 'design', 'interface design', 'techn |0 |0 |0 |
['children', 'design', 'education', 'entrepre |0 |0 |0 |
tags |instr(tags, 'ted') |locate('ted', tags) |locate('ted', tags, 5) |
----------------------------------------------|-------------------|--------------------|-----------------------|
['children', 'creativity', 'culture', 'dance' |0 |0 |0 |
['alternative energy', 'cars', 'climate chang |0 |0 |0 |
['computers', 'entertainment', 'interface des |0 |0 |0 |
['MacArthur grant', 'activism', 'business', ' |0 |0 |0 |
['Africa', 'Asia', 'Google', 'demo', 'economi |0 |0 |0 |
['business', 'culture', 'entertainment', 'goa |0 |0 |0 |
['Christianity', 'God', 'atheism', 'comedy', |0 |0 |0 |
['architecture', 'collaboration', 'culture', |0 |0 |0 |
['God', 'TED Brain Trust', 'atheism', 'brain' |10 |10 |10 |
['Christianity', 'God', 'culture', 'happiness |0 |0 |0 |
['activism', 'architecture', 'collaboration', |0 |0 |0 |
['TED Prize', 'art', 'culture', 'entertainmen |3 |3 |0 |
['TED Prize', 'collaboration', 'disease', 'eb |3 |3 |0 |
['demo', 'design', 'interface design', 'techn |0 |0 |0 |
['children', 'design', 'education', 'entrepre |0 |0 |0 |
tags |instr(tags, 'TED%') |locate('TED%', tags) |locate('TED%', tags, 5) |
----------------------------------------------|--------------------|---------------------|------------------------|
['children', 'creativity', 'culture', 'dance' |0 |0 |0 |
['alternative energy', 'cars', 'climate chang |0 |0 |0 |
['computers', 'entertainment', 'interface des |0 |0 |0 |
['MacArthur grant', 'activism', 'business', ' |0 |0 |0 |
['Africa', 'Asia', 'Google', 'demo', 'economi |0 |0 |0 |
['business', 'culture', 'entertainment', 'goa |0 |0 |0 |
['Christianity', 'God', 'atheism', 'comedy', |0 |0 |0 |
['architecture', 'collaboration', 'culture', |0 |0 |0 |
['God', 'TED Brain Trust', 'atheism', 'brain' |0 |0 |0 |
['Christianity', 'God', 'culture', 'happiness |0 |0 |0 |
['activism', 'architecture', 'collaboration', |0 |0 |0 |
['TED Prize', 'art', 'culture', 'entertainmen |0 |0 |0 |
['TED Prize', 'collaboration', 'disease', 'eb |0 |0 |0 |
['demo', 'design', 'interface design', 'techn |0 |0 |0 |
['children', 'design', 'education', 'entrepre |0 |0 |0 |
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment