Skip to content

Instantly share code, notes, and snippets.

@ateneva

ateneva/SQL_charindex_vs_patindex.sql

Last active September 2, 2018 13:44
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/dfb472f33cb551893e6f0cd3f0ddea25 to your computer and use it in GitHub Desktop.
Save ateneva/dfb472f33cb551893e6f0cd3f0ddea25 to your computer and use it in GitHub Desktop.
Download ZIP
What is the difference between charindex and patindex in SQL Server?
Raw
SQL_charindex_vs_patindex.sql
-------------------SQL Server-------------------------------------------
select
tags,
charindex('technology', tags) as char_position_tech,
patindex('technology', tags) as pat_position_tech,
patindex('%technology%', tags) as pat_position_tech_2,
charindex('Technology', tags) as char_position_Tech,
patindex('%Technology%', tags) as pat_position_Tech,
charindex('TED%', tags) as char_position_TED,
patindex('%TED%', tags) as pat_position_TED
from datageeking.dbo.ted_talks
/*tags |char_position_tech |pat_position_tech |pat_position_tech_2 |
-------------------------------------------------------------------------------------|-------------------|------------------|--------------------|
['alternative energy', 'energy', 'exploration', 'green', 'science', 'technology'] |70 |0 |70 |
['DNA', 'biology', 'business', 'genetics', 'life', 'science', 'technology'] |64 |0 |64 |
['TED Brain Trust', 'children', 'creativity', 'education', 'invention'] |0 |0 |0 |
['TEDx', 'business', 'creativity', 'culture', 'design', 'fashion', 'law', 'media'] |0 |0 |0 |
['TED Fellows', 'children', 'culture', 'film', 'politics', 'suicide', 'war'] |0 |0 |0 |
['AIDS', 'HIV', 'Vaccines', 'disease', 'global issues', 'health', 'medicine'] |0 |0 |0 |
['cities', 'music', 'performance', 'poetry', 'storytelling'] |0 |0 |0 |
['Internet', 'TEDx', 'law', 'music', 'online video', 'technology', 'web'] |55 |0 |55 |
tags |char_position_Tech |pat_position_Tech |
-------------------------------------------------------------------------------------|-------------------|------------------|
['alternative energy', 'energy', 'exploration', 'green', 'science', 'technology'] |70 |70 |
['DNA', 'biology', 'business', 'genetics', 'life', 'science', 'technology'] |64 |64 |
['TED Brain Trust', 'children', 'creativity', 'education', 'invention'] |0 |0 |
['TEDx', 'business', 'creativity', 'culture', 'design', 'fashion', 'law', 'media'] |0 |0 |
['TED Fellows', 'children', 'culture', 'film', 'politics', 'suicide', 'war'] |0 |0 |
['AIDS', 'HIV', 'Vaccines', 'disease', 'global issues', 'health', 'medicine'] |0 |0 |
['cities', 'music', 'performance', 'poetry', 'storytelling'] |0 |0 |
['Internet', 'TEDx', 'law', 'music', 'online video', 'technology', 'web'] |55 |55 |x
tags |char_position_TED |pat_position_TED
-------------------------------------------------------------------------------------|------------------|----------
['alternative energy', 'energy', 'exploration', 'green', 'science', 'technology'] |0 |0
['DNA', 'biology', 'business', 'genetics', 'life', 'science', 'technology'] |0 |0
['TED Brain Trust', 'children', 'creativity', 'education', 'invention'] |0 |3
['TEDx', 'business', 'creativity', 'culture', 'design', 'fashion', 'law', 'media'] |0 |3
['TED Fellows', 'children', 'culture', 'film', 'politics', 'suicide', 'war'] |0 |3
['AIDS', 'HIV', 'Vaccines', 'disease', 'global issues', 'health', 'medicine'] |0 |0
['cities', 'music', 'performance', 'poetry', 'storytelling'] |0 |0
['Internet', 'TEDx', 'law', 'music', 'online video', 'technology', 'web'] |0 |15
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment