Built-in Functions

Common CONVERT styles: 101 mm/dd/yyyy, 103 dd/mm/yyyy, 112 yyyymmdd, 120 ODBC canonical, 126 ISO8601.

8.1 String Functions

SELECT LEN('  SQL  '),          -- 5 (trailing spaces are ignored)
       DATALENGTH(N'SQL'),      -- 6 bytes
       UPPER('sql'), LOWER('SQL'),
       LTRIM('  a'), RTRIM('a  '), TRIM('  a  '),
       TRIM('x' FROM 'xxaxx'),                    -- 2017+
       LEFT('SQLServer', 3), RIGHT('SQLServer', 6),
       SUBSTRING('SQLServer', 4, 6),
       CHARINDEX('S', 'SQLServer', 2),            -- 4
       PATINDEX('%[0-9]%', 'abc123'),             -- 4
       REPLACE('a-b-c', '-', '/'),
       REPLICATE('*', 5),
       REVERSE('abc'),
       STUFF('abcdef', 2, 3, 'XYZ'),              -- aXYZef
       CONCAT('A', NULL, 'B'),                    -- 'AB' (NULL treated as '')
       CONCAT_WS(', ', 'Pune', NULL, 'India'),    -- 'Pune, India'
       'A' || 'B',                                -- 2025 ANSI concat operator
       FORMAT(1234567.891, 'N2', 'en-IN'),        -- 12,34,567.89
       QUOTENAME('My Table'),                     -- [My Table]
       TRANSLATE('2*[3+4]', '[]', '()'),          -- 2017+
       SPACE(3), ASCII('A'), CHAR(65), UNICODE(N'अ'), NCHAR(2309),
       SOUNDEX('Smith'), DIFFERENCE('Smith', 'Smyth'),
       STRING_ESCAPE('a"b', 'json');

-- STRING_AGG (2017+): concat
…

8.2 Regular Expression Functions (SQL Server 2025)

SELECT * FROM dbo.Customers WHERE REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
SELECT REGEXP_REPLACE('Phone: 98-765-43210', '[^0-9]', '');
SELECT REGEXP_SUBSTR('Order #A123 shipped', '[A-Z][0-9]+');
SELECT REGEXP_COUNT('banana', 'a');
SELECT REGEXP_INSTR('hello world', 'o');
SELECT * FROM REGEXP_MATCHES('a1b22c333', '[0-9]+');
SELECT * FROM REGEXP_SPLIT_TO_TABLE('a1b2c', '[0-9]');

8.3 Numeric / Mathematical Functions

SELECT ABS(-5), CEILING(4.1), FLOOR(4.9), ROUND(123.456, 2), ROUND(123.456, -1),
       ROUND(123.456, 2, 1),   -- truncate
       POWER(2, 10), SQRT(16), SQUARE(4), EXP(1), LOG(10), LOG10(100), LOG(8, 2),
       PI(), SIN(0), COS(0), RAND(), SIGN(-3),
       GREATEST(3, 7, 5), LEAST(3, 7, 5);          -- 2022+
SELECT 7 / 2, 7 / 2.0, 7 % 2;                     -- 3, 3.500000, 1  (integer division!)

8.4 Date and Time Functions

SELECT GETDATE(), SYSDATETIME(), GETUTCDATE(), SYSUTCDATETIME(), SYSDATETIMEOFFSET(),
       CURRENT_TIMESTAMP, CURRENT_DATE;           -- CURRENT_DATE is 2025+

SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()),
       DATEPART(QUARTER, GETDATE()), DATEPART(WEEKDAY, GETDATE()), DATEPART(ISO_WEEK, GETDATE()),
       DATENAME(MONTH, GETDATE()), DATENAME(WEEKDAY, GETDATE());

SELECT DATEADD(DAY, 30, GETDATE()), DATEADD(MONTH, -1, GETDATE()),
       DATEDIFF(DAY, '2025-01-01', '2025-12-31'),
       DATEDIFF_BIG(MILLISECOND, '2000-01-01', GETDATE()),
       EOMONTH(GETDATE()), EOMONTH(GETDATE(), -1),
       DATEFROMPARTS(2025, 12, 31), DATETIME2FROMPARTS(2025,12,31,23,59,59,0,0),
       ISDATE('2025-02-30'),
       DATETRUNC(MONTH, GETDATE()),               -- 2022+
       DATE_BUCKET(WEEK, 1, GETDATE()),            -- 2022+
       SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'),
       TODATETIMEOFFSET(GETDATE(), '+05:30'),
       GETDATE() AT TIME ZONE 'India Standard Time' AT TIME ZONE 'UTC';

SET DATEFIRST 1;      -- Monday = first day of week
SET DATEFORMAT dmy;
-- Language-safe date literal formats: 'YYYYMMDD' and 'YYYY-MM-DDThh:mm:ss'

8.5 Conversion Functions

Common CONVERT styles: 101 mm/dd/yyyy, 103 dd/mm/yyyy, 112 yyyymmdd, 120 ODBC canonical, 126 ISO8601.

SELECT CAST('123' AS INT),
       CONVERT(VARCHAR(10), GETDATE(), 103),    -- dd/mm/yyyy
       CONVERT(VARCHAR(19), GETDATE(), 120),    -- yyyy-mm-dd hh:mi:ss
       TRY_CAST('abc' AS INT),                  -- NULL instead of an error
       TRY_CONVERT(DATE, '2025-13-01'),         -- NULL
       PARSE('Monday, 13 December 2025' AS DATE USING 'en-US'),
       TRY_PARSE('12/31/2025' AS DATE USING 'en-US');

8.6 NULL-Related and Logical Functions

SELECT ISNULL(Email, 'N/A'),                -- 2 arguments, uses the type of the first
       COALESCE(Email, Phone, 'N/A'),       -- n arguments, ANSI, highest precedence type
       NULLIF(Stock, 0),                    -- NULL if equal (avoids divide-by-zero)
       IIF(Stock > 0, 'In Stock', 'Out'),
       CHOOSE(2, 'Low', 'Medium', 'High')   -- 'Medium'
FROM dbo.Customers c CROSS JOIN dbo.Products p;

SELECT Revenue / NULLIF(Units, 0) FROM dbo.Sales;

8.7 System and Metadata Functions

SELECT @@ROWCOUNT, @@ERROR, @@SPID, @@TRANCOUNT, @@SERVERNAME, @@VERSION,
       DB_NAME(), DB_ID('ShopDB'), OBJECT_ID('dbo.Customers'), OBJECT_NAME(245575913),
       SCHEMA_NAME(), USER_NAME(), SUSER_SNAME(), ORIGINAL_LOGIN(), HOST_NAME(), APP_NAME(),
       NEWID(), CHECKSUM(N'abc'), BINARY_CHECKSUM(*), HASHBYTES('SHA2_256', 'abc'),
       COL_LENGTH('dbo.Customers', 'Email'), COLUMNPROPERTY(OBJECT_ID('dbo.Customers'), 'CustomerID', 'IsIdentity'),
       SESSION_CONTEXT(N'TenantID'), CONTEXT_INFO(), COMPRESS('text'), DECOMPRESS(0x1F8B);

8.8 Fuzzy String Matching (SQL Server 2025)

SELECT EDIT_DISTANCE('kitten', 'sitting'),            -- 3
       EDIT_DISTANCE_SIMILARITY('kitten', 'sitting'),
       JARO_WINKLER_DISTANCE('Martha', 'Marhta'),
       JARO_WINKLER_SIMILARITY('Martha', 'Marhta');

8.9 Deterministic vs Non-deterministic Functions

Deterministic functions always return the same result for the same input (e.g., ABS, DATEADD).

Non-deterministic functions can return different results (e.g., GETDATE, NEWID, RAND).

Matters for indexed views, persisted computed columns, and function inlining.