Skip to content

Instantly share code, notes, and snippets.

@ateneva

ateneva/SQL_position_vs_strpos.sql

Last active September 2, 2018 13:12
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/35707abba9f3b6edd653bc9b4025d87c to your computer and use it in GitHub Desktop.
Save ateneva/35707abba9f3b6edd653bc9b4025d87c to your computer and use it in GitHub Desktop.
Download ZIP
What is the difference between position() and strpos() in PostgreSQL?
Raw
SQL_position_vs_strpos.sql
-------------------------------PostgreSQL-----------------------------------------
select
tags,
position('technology' in tags) as position_tech,
strpos(tags, 'technology') as strpos_tech
position('Technology' in tags) as position_Tech,
strpos(tags, 'Technology') as strpos_Tech,
position('TED%' in tags) as position_TED_wild,
strpos(tags, 'TED%') as strpos_TED_wild
from public.ted_talks
/* tags |position_tech |strpos_tech
------------------------------------------------------------------------------------------------------------------------------|--------------|----
['computers'; 'entertainment'; 'interface design'; 'media'; 'music'; 'performance'; implicity'; oftware'; 'technology'] |108 |108
['MacArthur grant'; 'activism'; 'business'; 'cities'; 'environment'; 'green'; 'inequality'; 'politics'; 'pollution'] |0 |0
['Africa'; 'Asia'; 'Google'; 'demo'; 'economics'; 'global development'; 'global issues'; 'health'; 'math'; tatistics'] |0 |0
['activism'; 'architecture'; 'collaboration'; 'culture'; 'design'; 'disaster relief'; 'global issues'; 'invention'] |0 |0
['TED Prize'; 'art'; 'culture'; 'entertainment'; 'film'; 'global issues'; 'movies'; 'peace'; ocial change'] |0 |0
['TED Prize'; 'collaboration'; 'disease'; 'ebola'; 'global issues'; 'health'; cience'; 'technology'] |89 |89
['demo'; 'design'; 'interface design'; 'technology'] |41 |41
['children'; 'design'; 'education'; 'entrepreneur'; 'global issues'; 'philanthropy'; ocial change'; 'technology'] |102 |102
['entertainment'; 'music'; 'performance'; 'violin'; 'wunderkind'; 'youth'] |0 |0
['creativity'; 'entertainment'; 'music'; 'performance'; 'piano'; 'wunderkind'] |0 |0
['business'; 'collaboration'; 'culture'; 'invention'; 'media'; 'open-source'; 'technology'; 'wikipedia'] |80 |80
['TED Fellows'; 'disease'; 'health care'; 'innovation'; 'invention'; 'medicine'; 'product design'] |0 |0
['business'; 'collaboration'; 'culture'; 'global issues'; 'library'; 'open-source'; 'technology'] |86 |86
['collaboration'; 'comedy'; 'community'; 'culture'; 'dance'; 'demo'; 'entertainment'; 'humor'; 'performance'] |0 |0
tags |position_Tech |strpos_Tech
------------------------------------------------------------------------------------------------------------------------------|--------------|----
['computers'; 'entertainment'; 'interface design'; 'media'; 'music'; 'performance'; implicity'; oftware'; 'technology'] |0 |0
['MacArthur grant'; 'activism'; 'business'; 'cities'; 'environment'; 'green'; 'inequality'; 'politics'; 'pollution'] |0 |0
['Africa'; 'Asia'; 'Google'; 'demo'; 'economics'; 'global development'; 'global issues'; 'health'; 'math'; tatistics'] |0 |0
['activism'; 'architecture'; 'collaboration'; 'culture'; 'design'; 'disaster relief'; 'global issues'; 'invention'] |0 |0
['TED Prize'; 'art'; 'culture'; 'entertainment'; 'film'; 'global issues'; 'movies'; 'peace'; ocial change'] |0 |0
['TED Prize'; 'collaboration'; 'disease'; 'ebola'; 'global issues'; 'health'; cience'; 'technology'] |0 |0
['demo'; 'design'; 'interface design'; 'technology'] |0 |0
['children'; 'design'; 'education'; 'entrepreneur'; 'global issues'; 'philanthropy'; ocial change'; 'technology'] |0 |0
['entertainment'; 'music'; 'performance'; 'violin'; 'wunderkind'; 'youth'] |0 |0
['creativity'; 'entertainment'; 'music'; 'performance'; 'piano'; 'wunderkind'] |0 |0
['business'; 'collaboration'; 'culture'; 'invention'; 'media'; 'open-source'; 'technology'; 'wikipedia'] |0 |0
['TED Fellows'; 'disease'; 'health care'; 'innovation'; 'invention'; 'medicine'; 'product design'] |0 |0
['business'; 'collaboration'; 'culture'; 'global issues'; 'library'; 'open-source'; 'technology'] |0 |0
['collaboration'; 'comedy'; 'community'; 'culture'; 'dance'; 'demo'; 'entertainment'; 'humor'; 'performance'] |0 |0
tags |position_TED_wild |strpos_TED_wild
------------------------------------------------------------------------------------------------------------------------------|------------------|----
['computers'; 'entertainment'; 'interface design'; 'media'; 'music'; 'performance'; implicity'; oftware'; 'technology'] |0 |0
['MacArthur grant'; 'activism'; 'business'; 'cities'; 'environment'; 'green'; 'inequality'; 'politics'; 'pollution'] |0 |0
['Africa'; 'Asia'; 'Google'; 'demo'; 'economics'; 'global development'; 'global issues'; 'health'; 'math'; tatistics'] |0 |0
['activism'; 'architecture'; 'collaboration'; 'culture'; 'design'; 'disaster relief'; 'global issues'; 'invention'] |0 |0
['TED Prize'; 'art'; 'culture'; 'entertainment'; 'film'; 'global issues'; 'movies'; 'peace'; ocial change'] |0 |0
['TED Prize'; 'collaboration'; 'disease'; 'ebola'; 'global issues'; 'health'; cience'; 'technology'] |0 |0
['demo'; 'design'; 'interface design'; 'technology'] |0 |0
['children'; 'design'; 'education'; 'entrepreneur'; 'global issues'; 'philanthropy'; ocial change'; 'technology'] |0 |0
['entertainment'; 'music'; 'performance'; 'violin'; 'wunderkind'; 'youth'] |0 |0
['creativity'; 'entertainment'; 'music'; 'performance'; 'piano'; 'wunderkind'] |0 |0
['business'; 'collaboration'; 'culture'; 'invention'; 'media'; 'open-source'; 'technology'; 'wikipedia'] |0 |0
['TED Fellows'; 'disease'; 'health care'; 'innovation'; 'invention'; 'medicine'; 'product design'] |0 |0
['business'; 'collaboration'; 'culture'; 'global issues'; 'library'; 'open-source'; 'technology'] |0 |0
['collaboration'; 'comedy'; 'community'; 'culture'; 'dance'; 'demo'; 'entertainment'; 'humor'; 'performance'] |0 |0
*
***/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment