Reviews String SQl

DataFrameas

df

variable

-- Explore the data in the table
SELECT *
FROM all_weeks_global
LIMIT 5

DataFrameas

df1

variable

-- Example query using STRPOS to find the position of a substring within a string
-- This query will find the position of the substring 'show' in the 'show_title' column from the 'all_weeks_global' table

SELECT show_title, STRPOS(show_title, 'of') AS position
FROM all_weeks_global;

DataFrameas

df2

variable

SELECT show_title,
STRPOS(show_title, 'Sing') AS position_sing
FROM public.all_weeks_global;

DataFrameas

df3

variable

SELECT
STRPOS('This is text to find string position of substring from text', 'text');

DataFrameas

df4

variable

SELECT
STRPOS('This is text to find string position of substring from text',  'string')

-- The STRPOS() function in PostgreSQL returns the position of the first occurrence of a substring within a string.
-- The position is 1-based, meaning it starts counting from 1, not 0.
-- Here is an example to illustrate how PostgreSQL counts the position:

-- Example string: 'This is a sample text'
-- Substring to find: 'sample'

SELECT STRPOS('This is a sample text', 'sample') AS position;

-- The result will be 11 because the substring 'sample' starts at the 11th character of the string.

-- To determine if PostgreSQL counts spaces when using STRPOS, we can run a simple query. -- This query will check the position of a space character within a string.

SELECT STRPOS('This is a test string', ' ') AS position_of_space;

DataFrameas

df5

variable

-- Query to extract a substring from the 'show_title' column in the 'all_weeks_countries' table
SELECT 
    country_name, 
    country_iso2, 
    week, 
    category, 
    weekly_rank, 
    SUBSTRING(show_title FROM 1 FOR 10) AS show_title_substring, 
    season_title, 
    cumulative_weeks_in_top_10
FROM 
    all_weeks_countries;

-- Query to extract a substring from the 'show_title' column in the 'all_weeks_global' table
SELECT 
    week, 
    category, 
    weekly_rank, 
    SUBSTRING(show_title FROM 1 FOR 10) AS show_title_substring, 
    season_title, 
    weekly_hours_viewed, 
    cumulative_weeks_in_top_10
FROM 
    all_weeks_global;

-- Query to extract a substring from the 'show_title' column in the 'most_popular' table
SELECT 
    category, 
    rank, 
    SUBSTRING(show_title FROM 1 FOR 10) AS show_title_substring, 
    season_title, 
    hours_viewed_first_28_days
FROM 
    most_popular;

DataFrameas

df8

variable

SELECT
STRPOS('This is a test string', ' ') AS position_of_space;

DataFrameas

df6

variable

SELECT
SUBSTRING('This is text to find substring from text'FROM 2 FOR 8 );

DataFrameas

df7

variable

SELECT
SUBSTRING('This is text to find substring from text'FROM 5 FOR 12 );

DataFrameas

df9

variable

-- Example query using SPLIT_PART() to extract the season number from the season_title column in the all_weeks_global table
SELECT 
    show_title,
    season_title,
    SPLIT_PART(show_title, ' ', 1) AS season_number
FROM 
    all_weeks_global
--WHERE 
    --category = 'TV'
ORDER BY 
    weekly_rank;

DataFrameas

df10

variable

SELECT
SPLIT_PART('JohnDoe CompanyWORK NewYork City Street CityBark', ' ', 2);

DataFrameas

df11

variable

SELECT
SPLIT_PART('JohnDoe CompanyWORK NewYork City Street CityBark', ' ', 3);

DataFrameas

df12

variable

SELECT
SPLIT_PART('JohnDoe CompanyWORK NewYork City Street CityBark', ' ', 5);

‌
‌
‌