Ecolab SQL Interview Question - SUBSTRING() and CHARINDEX() Functions

[@yashmathur5609]

Hi cloud challengers, I am writing this comment to appreciate your efforts, I've recently started seeing your videos for sql and in every video I'm learning some new functions or logic. Thanks for coming up with such a great explanation and interview questions.🙌🙌

[@CloudChallengers]

@yashmathur5609, Thank you for the feedback, it means a lot to me. 🙂

[@ishanshubham8355]

select right(email,length(email)-locate("@",email)) as domain from customer_tbl;

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@sudhindrab1606]

with cte as (select value from customer_tbl cross apply string_split(email,'@') ) select * from cte where value like '%.%'

[@snehsparsh7954]

Email Validation(Counter Question at end): SELECT email, CASE WHEN REGEXP_CONTAINS(email, r'^[^@]+@[^@]+\.[^@]+$') -- Checkpoints 1, 5, and partially 6 (basic structure) AND NOT REGEXP_CONTAINS(email, r'@{2}') -- Checkpoint 2 AND NOT REGEXP_CONTAINS(email, r'\.{2}') -- Checkpoint 3 AND NOT REGEXP_CONTAINS(email, r'[@\.][\.@]|^\.|\.$') -- Checkpoint 4 and partially 6 (no adjacent or leading/trailing . or @) AND NOT REGEXP_CONTAINS(email, r'[^\w\.\+\-\@]') -- Checkpoint 6 (invalid characters) THEN 'Valid' ELSE 'Invalid' END AS email_validity FROM practiceDB1.customer_tbl;

[@gkapadi9831]

In oracle sql - Select substr(email,instr(email,'@')+1) as domain from customer_tbl;

[@MaheshNaidu-j3x]

In snowflake--- SELECT SPLIT_PART(email, '@', 2) AS domain FROM customer_tbl;

[@suprisahana]

Kindly add your query

[@hairavyadav6579]

select substring_index(email,"@",-1) as domain from customer_tbl; select substr(email,locate("@",email)+1) as domain from customer_tbl; select right(email,(length(email) - locate("@",email))) as domain from customer_tbl; My approach

[@anushas1910]

select substring_index(email,'@',-1) as domain from customer_tbl ;

[@motiali6855]

My sql Solution select *,substring_index(email,"@",-1) from customer_tbl;

[@sravankumar1767]

Select email, case when email like '%@%' and email like '%.%' and email not like '%@%@%' and email not like '%.%.%' and email not like '%@.%' and email not like '%.@%' and email not like '%(^a-zA-Z0-9@._-)%' Then 'Valid' else 'Invalid' end as email status from table_name

[@djsahu98]

What if 3 @ or 3 . are there

[@sravankumar1767]

​@@djsahu98 u can add it email like '%_@_%' it represents atleast one character before and after @

[@naraharivishwanath]

​@@djsahu98 if there are 3@ then this would be an invalid and in the above logic it's satisfied in the case conditions like %@%@% so it will return an invalid as per my knowledge.

[@mrigankashekhardas9246]

SELECT email, CASE WHEN email LIKE '%_@_%_.__%' THEN 'Valid' ELSE 'Invalid' END AS e_status FROM users;