FazBrowse GitHub Viewer
|
Trending
|
URL:
|
Home
Tools:
[Download Repo ZIP]
[View Raw Code]
[Original HTTPS Page]
sql-ultimate-course/scripts/07_String_Functions.sql at main · sindoc/sql-ultimate-course · GitHub
sindoc
/
sql-ultimate-course
Public
forked from
DataWithBaraa/sql-ultimate-course
Notifications
You must be signed in to change notification settings
Fork
0
Star
1
Code
Pull requests
0
Actions
Projects
Security and quality
0
Insights
Additional navigation options
Code
Pull requests
Actions
Projects
Security and quality
Insights
Expand file tree
Breadcrumbs
sql-ultimate-course
/
scripts
/
07_String_Functions.sql
Copy path
More file actions
More file actions
Latest commit
History
History
History
117 lines (98 loc) · 3.9 KB
Breadcrumbs
sql-ultimate-course
/
scripts
/
07_String_Functions.sql
Copy path
File metadata and controls
117 lines (98 loc) · 3.9 KB
Raw
Copy raw file
Download raw file
Open symbols panel
Edit and raw actions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
/*
==============================================================================
SQL String Functions
-------------------------------------------------------------------------------
This document provides an overview of SQL string functions, which allow
manipulation, transformation, and extraction of text data efficiently.
Table of Contents:
1. Manipulations
- CONCAT
- LOWER
- UPPER
- TRIM
- REPLACE
2. Calculation
- LEN
3. Substring Extraction
- LEFT
- RIGHT
- SUBSTRING
=================================================================================
*/
/*
==============================================================================
CONCAT() - String Concatenation
===============================================================================
*/
--
Concatenate first name and country into one column
SELECT
CONCAT(first_name,
'
-
'
, country)
AS
full_info
FROM
customers
/*
==============================================================================
LOWER() & UPPER() - Case Transformation
===============================================================================
*/
--
Convert the first name to lowercase
SELECT
LOWER
(first_name)
AS
lower_case_name
FROM
customers
--
Convert the first name to uppercase
SELECT
UPPER
(first_name)
AS
upper_case_name
FROM
customers
/*
==============================================================================
TRIM() - Remove White Spaces
===============================================================================
*/
--
Find customers whose first name contains leading or trailing spaces
SELECT
first_name,
LEN(first_name) len_name,
LEN(
TRIM
(first_name)) len_trim_name,
LEN(first_name)
-
LEN(
TRIM
(first_name)) flag
FROM
customers
WHERE
LEN(first_name)
!=
LEN(
TRIM
(first_name))
--
WHERE first_name != TRIM(first_name)
/*
==============================================================================
REPLACE() - Replace or Remove old value with new one
===============================================================================
*/
--
Remove dashes (-) from a phone number
SELECT
'
123-456-7890
'
AS
phone,
REPLACE(
'
123-456-7890
'
,
'
-
'
,
'
/
'
)
AS
clean_phone
--
Replace File Extence from txt to csv
SELECT
'
report.txt
'
AS
old_filename,
REPLACE(
'
report.txt
'
,
'
.txt
'
,
'
.csv
'
)
AS
new_filename
/*
==============================================================================
LEN() - String Length & Trimming
===============================================================================
*/
--
Calculate the length of each customer's first name
SELECT
first_name,
LEN(first_name)
AS
name_length
FROM
customers
/*
==============================================================================
LEFT() & RIGHT() - Substring Extraction
===============================================================================
*/
--
Retrieve the first two characters of each first name
SELECT
first_name,
LEFT(
TRIM
(first_name),
2
)
AS
first_2_chars
FROM
customers
--
Retrieve the last two characters of each first name
SELECT
first_name,
RIGHT(first_name,
2
)
AS
last_2_chars
FROM
customers
/*
==============================================================================
SUBSTRING() - Extracting Substrings
===============================================================================
*/
--
Retrieve a list of customers' first names after removing the first character
SELECT
first_name,
SUBSTRING
(
TRIM
(first_name),
2
, LEN(first_name))
AS
trimmed_name
FROM
customers
/*
==============================================================================
NESTING FUNCTIONS
===============================================================================
*/
--
Nesting
SELECT
first_name,
UPPER
(
LOWER
(first_name))
AS
nesting
FROM
customers
Back
|
FazBrowse Home
|
New Git URL