This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select email, email regexp '.+@.+\..+' as matches | |
from users; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
REGEXP | |
מחזירה 'אמת' אם הערך מתאים לתבנית שהוגדרה | |
true | |
<subject> REGEXP <pattern> | |
REGEXP_REPLACE | |
מחזירה את הערך הנבחר עם החלפה של התבנית שהוגדרה בתחליף שהוגדר, אם אין התאמה מחזירה את הערך המקורי | |
REGEXP_REPLACE( <subject> , <pattern> [ , <replacement> , <position> , <occurrence> , <parameters> ] ) | |
REGEXP_COUNT |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Regex סימנים מוסכמים ב | |
כמובן שמדובר ברשימה חלקית של סימנים עיקריים | |
$ קובע מיקום בסוף השורה | |
^ קובע מיקום בתחילת השורה | |
. מתאים לכל תו | |
* תואם את הסימון שלפניו בין אפס פעמים לבין מספר פעמים בלתי מוגבל | |
+ תואם את הסימון שלפניו בין פעם אחת לבין מספר פעמים בלתי מוגבל | |
\d: ספרה עשרונית (0-9) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- one raw comment | |
/* start a comment | |
end */ | |
select 1; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT name | |
, CASE WHEN age < 1 THEN "baby" | |
WHEN age BETWEEN 1 and 6 THEN "todler" | |
WHEN age BETWEEN 6 and 12 THEN "child" | |
WHEN age BETWEEN 12 and 18 THEN "adolescent" | |
WHEN age > 18 then "adult" | |
END AS age_ | |
FROM population; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TEMPORARY FUNCTION age_range(age INT) AS ( | |
CASE WHEN age < 1 THEN "baby" | |
WHEN age BETWEEN 1 and 6 THEN "todler" | |
WHEN age BETWEEN 6 and 12 THEN "child" | |
WHEN age BETWEEN 12 and 18 THEN "adolescent" | |
WHEN age > 18 then "adult" | |
END | |
); | |
SELECT name |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with usa_ent_account as( | |
SELECT DISTINCT account_id | |
FROM accounts | |
WHERE account_type = "enterprize" | |
AND country = "USA" | |
) | |
, orders_per_user as ( | |
select user_id | |
, count(*) as num_of_orders |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT user_id | |
, user_name | |
, account_id | |
FROM users | |
left join ( | |
select user_id | |
, count(*) as num_of_orders | |
from orders | |
group by user_id opu | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT account_id | |
, subscription_date | |
, num_of_seats | |
, CASE WHEN num_of_seats>1500 THEN "enterprize" ELSE "normal" END AS accountType | |
, plan_id | |
, plan_name | |
FROM accounts a | |
LEFT JOIN plans p | |
on a.plan_id = p.plan_id | |
WHERE year(subscription_date)>='2020' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT account_id,subscription_date,num_of_seats, CASE WHEN num_of_seats>1500 THEN "enterprize" ELSE "normal" END AS accountType,plan_id | |
FROM accounts a LEFT JOIN plans p on a.plan_id = p.plan_id WHERE year(subscription_date)>='2020'; |
NewerOlder