0
# SQL Functions
1

2
Comprehensive collection of SQL functions including aggregate functions (COUNT, SUM, AVG), string functions (CONCAT, SUBSTRING), date/time functions (NOW, DATE_ADD), mathematical functions (ABS, SQRT), and type conversion functions. These functions provide database-agnostic SQL functionality with dialect-specific optimizations.
3

4
## Capabilities
5

6
### Aggregate Functions
7

8
SQL aggregate functions for data summarization and statistical calculations.
9

10
```python { .api }
11
class Count(AggregateFunction):
12
    def __init__(self, term, alias=None):
13
        """COUNT function with DISTINCT support."""
14
        
15
    def distinct(self) -> Count:
16
        """Add DISTINCT modifier."""
17

18
class Sum(AggregateFunction):
19
    def __init__(self, term, alias=None):
20
        """SUM function with DISTINCT support."""
21
        
22
    def distinct(self) -> Sum:
23
        """Add DISTINCT modifier."""
24

25
class Avg(AggregateFunction):
26
    def __init__(self, term, alias=None):
27
        """AVG function."""
28

29
class Min(AggregateFunction):
30
    def __init__(self, term, alias=None):
31
        """MIN function."""
32

33
class Max(AggregateFunction):
34
    def __init__(self, term, alias=None):
35
        """MAX function."""
36

37
class Std(AggregateFunction):
38
    def __init__(self, term, alias=None):
39
        """Standard deviation function."""
40

41
class StdDev(AggregateFunction):
42
    def __init__(self, term, alias=None):
43
        """STDDEV function."""
44

45
class First(AggregateFunction):
46
    def __init__(self, term, alias=None):
47
        """FIRST function."""
48

49
class Last(AggregateFunction):
50
    def __init__(self, term, alias=None):
51
        """LAST function."""
52
```
53

54
**Usage Examples:**
55

56
```python
57
from pypika import Query, Table
58
from pypika.functions import Count, Sum, Avg, Min, Max, StdDev
59

60
orders = Table('orders')
61
products = Table('products')
62
users = Table('users')
63

64
# Basic aggregate functions
65
query = (Query.from_(orders)
66
         .select(
67
             Count('*').as_('total_orders'),
68
             Sum(orders.amount).as_('total_revenue'),
69
             Avg(orders.amount).as_('avg_order_value'),
70
             Min(orders.amount).as_('min_order'),
71
             Max(orders.amount).as_('max_order')
72
         ))
73

74
# COUNT with DISTINCT
75
query = (Query.from_(orders)
76
         .select(
77
             Count(orders.customer_id).distinct().as_('unique_customers'),
78
             Count('*').as_('total_orders')
79
         ))
80

81
# SUM with DISTINCT
82
query = (Query.from_(orders)
83
         .select(
84
             Sum(orders.amount).distinct().as_('unique_order_amounts')
85
         ))
86

87
# Grouped aggregations
88
query = (Query.from_(orders)
89
         .select(
90
             orders.customer_id,
91
             Count('*').as_('order_count'),
92
             Sum(orders.amount).as_('total_spent'),
93
             Avg(orders.amount).as_('avg_order_value'),
94
             StdDev(orders.amount).as_('order_amount_stddev')
95
         )
96
         .groupby(orders.customer_id))
97

98
# HAVING with aggregates
99
query = (Query.from_(orders)
100
         .select(
101
             orders.customer_id,
102
             Count('*').as_('order_count'),
103
             Sum(orders.amount).as_('total_spent')
104
         )
105
         .groupby(orders.customer_id)
106
         .having(Count('*') > 5)
107
         .having(Sum(orders.amount) > 1000))
108
```
109

110
### String Functions
111

112
String manipulation and text processing functions.
113

114
```python { .api }
115
class Concat(Function):
116
    def __init__(self, *terms):
117
        """Concatenate strings."""
118

119
class Upper(Function):
120
    def __init__(self, term):
121
        """Convert to uppercase."""
122

123
class Lower(Function):
124
    def __init__(self, term):
125
        """Convert to lowercase."""
126

127
class Length(Function):
128
    def __init__(self, term):
129
        """Get string length."""
130

131
class Substring(Function):
132
    def __init__(self, term, start, length=None):
133
        """Extract substring."""
134

135
class Trim(Function):
136
    def __init__(self, term, chars=None):
137
        """Trim whitespace or specified characters."""
138

139
class Replace(Function):
140
    def __init__(self, term, search, replacement):
141
        """Replace text in string."""
142

143
class Reverse(Function):
144
    def __init__(self, term):
145
        """Reverse string."""
146

147
class Ascii(Function):
148
    def __init__(self, term):
149
        """Get ASCII value of first character."""
150

151
class Insert(Function):
152
    def __init__(self, term, position, length, new_string):
153
        """Insert string at position."""
154

155
class SplitPart(Function):
156
    def __init__(self, term, delimiter, field_number):
157
        """Split string and return part."""
158

159
class RegexpMatches(Function):
160
    def __init__(self, term, pattern, flags=None):
161
        """Regular expression matches."""
162

163
class RegexpLike(Function):
164
    def __init__(self, term, pattern, flags=None):
165
        """Regular expression like."""
166
```
167

168
**Usage Examples:**
169

170
```python
171
from pypika import Query, Table
172
from pypika.functions import (
173
    Concat, Upper, Lower, Length, Substring, Trim, Replace, 
174
    Reverse, SplitPart, RegexpLike
175
)
176

177
users = Table('users')
178
products = Table('products')
179

180
# String concatenation
181
query = (Query.from_(users)
182
         .select(
183
             Concat(users.first_name, ' ', users.last_name).as_('full_name'),
184
             Concat('User: ', users.username).as_('display_name')
185
         ))
186

187
# Case conversion
188
query = (Query.from_(users)
189
         .select(
190
             Upper(users.first_name).as_('first_name_upper'),
191
             Lower(users.email).as_('email_lower')
192
         ))
193

194
# String analysis
195
query = (Query.from_(products)
196
         .select(
197
             products.name,
198
             Length(products.name).as_('name_length'),
199
             Length(products.description).as_('desc_length')
200
         ))
201

202
# Substring extraction
203
query = (Query.from_(users)
204
         .select(
205
             users.email,
206
             Substring(users.email, 1, 3).as_('email_prefix'),
207
             Substring(users.phone, -4).as_('last_four_digits')
208
         ))
209

210
# String cleaning
211
query = (Query.from_(users)
212
         .select(
213
             Trim(users.first_name).as_('trimmed_name'),
214
             Replace(users.phone, '-', '').as_('phone_digits_only'),
215
             Replace(users.email, '@gmail.com', '@company.com').as_('company_email')
216
         ))
217

218
# String manipulation
219
query = (Query.from_(products)
220
         .select(
221
             products.code,
222
             Reverse(products.code).as_('reversed_code')
223
         ))
224

225
# String splitting and pattern matching
226
query = (Query.from_(users)
227
         .select(
228
             users.email,
229
             SplitPart(users.email, '@', 1).as_('username'),
230
             SplitPart(users.email, '@', 2).as_('domain')
231
         )
232
         .where(RegexpLike(users.email, r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$')))
233
```
234

235
### Date and Time Functions
236

237
Date and time manipulation functions for temporal data processing.
238

239
```python { .api }
240
class Now(Function):
241
    def __init__(self):
242
        """Current timestamp."""
243

244
class CurDate(Function):
245
    def __init__(self):
246
        """Current date."""
247

248
class CurTime(Function):
249
    def __init__(self):
250
        """Current time."""
251

252
class Date(Function):
253
    def __init__(self, term):
254
        """Extract date part."""
255

256
class Timestamp(Function):
257
    def __init__(self, term):
258
        """Convert to timestamp."""
259

260
class DateAdd(Function):
261
    def __init__(self, date_part, interval, date_term):
262
        """Add interval to date."""
263

264
class DateDiff(Function):
265
    def __init__(self, date_part, start_date, end_date):
266
        """Calculate date difference."""
267

268
class TimeDiff(Function):
269
    def __init__(self, start_time, end_time):
270
        """Calculate time difference."""
271

272
class Extract(Function):
273
    def __init__(self, date_part, term):
274
        """Extract date part."""
275

276
class ToDate(Function):
277
    def __init__(self, term, format_mask=None):
278
        """Convert to date."""
279

280
class TimestampAdd(Function):
281
    def __init__(self, unit, interval, timestamp):
282
        """Add interval to timestamp."""
283

284
class UtcTimestamp(Function):
285
    def __init__(self):
286
        """Current UTC timestamp."""
287

288
class CurTimestamp(Function):
289
    def __init__(self):
290
        """Current timestamp."""
291
```
292

293
**Usage Examples:**
294

295
```python
296
from pypika import Query, Table, DatePart
297
from pypika.functions import (
298
    Now, CurDate, CurTime, Date, DateAdd, DateDiff, Extract, 
299
    ToDate, TimestampAdd, UtcTimestamp
300
)
301

302
users = Table('users')
303
orders = Table('orders')
304
events = Table('events')
305

306
# Current date/time functions
307
query = (Query.from_(users)
308
         .select(
309
             users.name,
310
             Now().as_('current_timestamp'),
311
             CurDate().as_('current_date'),
312
             CurTime().as_('current_time'),
313
             UtcTimestamp().as_('utc_timestamp')
314
         ))
315

316
# Date extraction
317
query = (Query.from_(orders)
318
         .select(
319
             orders.id,
320
             Date(orders.created_at).as_('order_date'),
321
             Extract(DatePart.year, orders.created_at).as_('order_year'),
322
             Extract(DatePart.month, orders.created_at).as_('order_month'),
323
             Extract(DatePart.day, orders.created_at).as_('order_day')
324
         ))
325

326
# Date arithmetic
327
query = (Query.from_(users)
328
         .select(
329
             users.name,
330
             users.created_at,
331
             DateAdd(DatePart.day, 30, users.created_at).as_('plus_30_days'),
332
             DateAdd(DatePart.month, 1, users.created_at).as_('plus_1_month'),
333
             DateAdd(DatePart.year, -1, Now()).as_('one_year_ago')
334
         ))
335

336
# Date differences
337
query = (Query.from_(users)
338
         .select(
339
             users.name,
340
             users.created_at,
341
             DateDiff(DatePart.day, users.created_at, Now()).as_('account_age_days'),
342
             DateDiff(DatePart.month, users.created_at, Now()).as_('account_age_months')
343
         ))
344

345
# Time-based filtering
346
thirty_days_ago = DateAdd(DatePart.day, -30, Now())
347
query = (Query.from_(orders)
348
         .select('*')
349
         .where(orders.created_at > thirty_days_ago))
350

351
# Grouping by date parts
352
query = (Query.from_(orders)
353
         .select(
354
             Extract(DatePart.year, orders.created_at).as_('year'),
355
             Extract(DatePart.month, orders.created_at).as_('month'),
356
             Count('*').as_('order_count'),
357
             Sum(orders.amount).as_('monthly_revenue')
358
         )
359
         .groupby(
360
             Extract(DatePart.year, orders.created_at),
361
             Extract(DatePart.month, orders.created_at)
362
         )
363
         .orderby('year', 'month'))
364

365
# String to date conversion
366
query = (Query.from_(events)
367
         .select(
368
             events.id,
369
             ToDate(events.date_string, 'YYYY-MM-DD').as_('parsed_date')
370
         )
371
         .where(ToDate(events.date_string, 'YYYY-MM-DD') > '2023-01-01'))
372
```
373

374
### Mathematical Functions
375

376
Mathematical and arithmetic functions for numerical calculations.
377

378
```python { .api }
379
class Abs(Function):
380
    def __init__(self, term):
381
        """Absolute value."""
382

383
class Sqrt(Function):
384
    def __init__(self, term):
385
        """Square root."""
386

387
class Floor(Function):
388
    def __init__(self, term):
389
        """Floor function."""
390

391
class Ceil(Function):
392
    def __init__(self, term):
393
        """Ceiling function."""
394

395
class Round(Function):
396
    def __init__(self, term, precision=None):
397
        """Round to specified precision."""
398

399
class Power(Function):
400
    def __init__(self, base, exponent):
401
        """Power function."""
402

403
class Mod(Function):
404
    def __init__(self, dividend, divisor):
405
        """Modulo operation."""
406

407
class ApproximatePercentile(Function):
408
    def __init__(self, term, percentile):
409
        """Approximate percentile calculation."""
410
```
411

412
**Usage Examples:**
413

414
```python
415
from pypika import Query, Table
416
from pypika.functions import Abs, Sqrt, Floor, Round, Power, Mod
417

418
orders = Table('orders')
419
products = Table('products')
420
metrics = Table('metrics')
421

422
# Basic math functions
423
query = (Query.from_(orders)
424
         .select(
425
             orders.amount,
426
             Abs(orders.discount).as_('abs_discount'),
427
             Sqrt(orders.quantity).as_('sqrt_quantity'),
428
             Floor(orders.amount).as_('amount_floor'),
429
             Round(orders.amount, 2).as_('amount_rounded')
430
         ))
431

432
# Advanced calculations
433
query = (Query.from_(products)
434
         .select(
435
             products.name,
436
             products.price,
437
             Power(products.price, 2).as_('price_squared'),
438
             Mod(products.id, 10).as_('id_mod_10')
439
         ))
440

441
# Financial calculations
442
query = (Query.from_(orders)
443
         .select(
444
             orders.customer_id,
445
             Round(Avg(orders.amount), 2).as_('avg_order_value'),
446
             Round(Sqrt(Sum(Power(orders.amount - Avg(orders.amount), 2)) / Count('*')), 2).as_('std_dev')
447
         )
448
         .groupby(orders.customer_id))
449
```
450

451
### Type Conversion Functions
452

453
Functions for converting between different data types.
454

455
```python { .api }
456
class Cast(Function):
457
    def __init__(self, term, as_type):
458
        """CAST function for type conversion."""
459

460
class Convert(Function):
461
    def __init__(self, term, as_type):
462
        """CONVERT function for type conversion."""
463

464
class ToChar(Function):
465
    def __init__(self, term, format_mask=None):
466
        """Convert to character string."""
467

468
class Signed(Function):
469
    def __init__(self, term):
470
        """Convert to signed integer."""
471

472
class Unsigned(Function):
473
    def __init__(self, term):
474
        """Convert to unsigned integer."""
475
```
476

477
**Usage Examples:**
478

479
```python
480
from pypika import Query, Table
481
from pypika.functions import Cast, Convert, ToChar, Signed
482

483
users = Table('users')
484
orders = Table('orders')
485

486
# Type casting
487
query = (Query.from_(users)
488
         .select(
489
             users.id,
490
             Cast(users.id, 'VARCHAR').as_('id_string'),
491
             Cast(users.created_at, 'DATE').as_('created_date'),
492
             Cast(users.score, 'INTEGER').as_('score_int')
493
         ))
494

495
# String formatting
496
query = (Query.from_(orders)
497
         .select(
498
             orders.id,
499
             ToChar(orders.created_at, 'YYYY-MM-DD').as_('formatted_date'),
500
             ToChar(orders.amount, '999,999.99').as_('formatted_amount')
501
         ))
502

503
# Numeric conversions
504
query = (Query.from_(users)
505
         .select(
506
             users.id,
507
             Signed(users.score).as_('signed_score')
508
         ))
509
```
510

511
### Null Handling Functions
512

513
Functions for handling NULL values and providing default values.
514

515
```python { .api }
516
class IsNull(Function):
517
    def __init__(self, term):
518
        """Check if value is NULL."""
519

520
class Coalesce(Function):
521
    def __init__(self, *terms):
522
        """Return first non-NULL value."""
523

524
class IfNull(Function):
525
    def __init__(self, term, replacement):
526
        """Replace NULL with specified value."""
527

528
class NullIf(Function):
529
    def __init__(self, term1, term2):
530
        """Return NULL if values are equal."""
531

532
class NVL(Function):
533
    def __init__(self, term, replacement):
534
        """Oracle-style NULL value replacement."""
535
```
536

537
**Usage Examples:**
538

539
```python
540
from pypika import Query, Table, NULL
541
from pypika.functions import Coalesce, IfNull, NullIf
542

543
users = Table('users')
544
orders = Table('orders')
545

546
# NULL handling
547
query = (Query.from_(users)
548
         .select(
549
             users.id,
550
             Coalesce(users.nickname, users.first_name, 'Anonymous').as_('display_name'),
551
             IfNull(users.phone, 'No phone').as_('phone_display'),
552
             NullIf(users.middle_name, '').as_('middle_name_cleaned')
553
         ))
554

555
# Default values for aggregates
556
query = (Query.from_(orders)
557
         .select(
558
             orders.customer_id,
559
             Coalesce(Sum(orders.amount), 0).as_('total_spent'),
560
             Coalesce(Count(orders.id), 0).as_('order_count')
561
         )
562
         .groupby(orders.customer_id))
563

564
# Conditional null replacement
565
query = (Query.from_(users)
566
         .select(
567
             users.name,
568
             Coalesce(
569
                 NullIf(users.preferred_email, ''),
570
                 users.email,
571
                 'no-email@company.com'
572
             ).as_('contact_email')
573
         ))
574
```
575

576
### Utility Functions
577

578
Additional utility functions for specialized operations.
579

580
```python { .api }
581
class Bin(Function):
582
    def __init__(self, term):
583
        """Convert to binary representation."""
584

585
class Ascii(Function):
586
    def __init__(self, term):
587
        """Get ASCII value."""
588

589
class Insert(Function):
590
    def __init__(self, term, position, length, new_string):
591
        """Insert string at position."""
592
```
593

594
**Usage Examples:**
595

596
```python
597
from pypika import Query, Table
598
from pypika.functions import Bin, Ascii
599

600
users = Table('users')
601

602
# Utility functions
603
query = (Query.from_(users)
604
         .select(
605
             users.id,
606
             Bin(users.id).as_('id_binary'),
607
             Ascii(users.first_name).as_('first_char_ascii')
608
         ))
609
```