-
-
Notifications
You must be signed in to change notification settings - Fork 624
Expand file tree
/
Copy path07_String_Functions.sql
More file actions
117 lines (98 loc) · 3.9 KB
/
Copy path07_String_Functions.sql
File metadata and controls
117 lines (98 loc) · 3.9 KB
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