Functions, SQL Simantics

SQL Server: Fake a Dynamic IN with STRING_SPLIT

Randy Sims SQL Server

use string_split to actually join your multiple choices to a table! #SQLServer2016 #SQL #TSQL

SQL Server: Duplicate Rows with Your Own Values Using STRING_SPLIT and CROSS JOIN

Randy Sims SQL Server

SQL Server 2016 came out with the STRING_SPLIT function that will return what amounts to a table from character-separated string. Using a CROSS JOIN with this function, you can return one row multiple times with whatever values you want!

SQL Server 2016’s STRING_SPLIT

Randy Sims SQL Server

I found a pretty cool little function as I was perusing the SQL Server functions documentation the other day. (What, doesn’t everyone do that?) I just read the name of this function and thought, well, no need to create that function with a loop any longer! Finally, SPLIT has come to SQL Server! What is […]

Oracle Single Row Character Functions Returning Character Values

Randy Sims SQL Fundamentals I

Exam Topic: Using Single-Row Functions to Customize Output – Describe various types of functions available in SQL Single row functions return a result for every row in a result. They can be used in any part of a SQL query, such as the SELECT lists or any part that can use a conditional statement. Oracle 11g has […]

Basic Algebraic Functions

Randy Sims SQL Fundamentals I

The following is a mini-workout of the basic Algebraic functions found in Oracle 11g. It includes ABS, CEILING, FLOOR, MOD, REMAINDER, NANVL, POWER, ROUND, SIGN, TRUNC and BUCKET_WIDTH. More functions can be found here.

Convert Decimal to Other Number Systems

Randy Sims Oracle, SQL Fundamentals I

Just for fun, I made a block of code that will convert decimal numbers to any other number system. Maybe I’ll go the other direction next. It’s basically a loop. It takes the remainder of the original number divided by the number system (MOD) and adds that to a string. The string is made by […]

Generate Random Values

Randy Sims Oracle

One of the best builtin packages in Oracle for anyone who needs to create random data is DBMS_RANDOM. Not only can you use it to create random values, but you can also use it to make random selections. For instance, if you have a list of values that you want to assign randomly, you can weight […]

Oracle Single Row Numeric Functions

Randy Sims SQL Fundamentals I

Exam Topic: Using Single-Row Functions to Customize Output – Describe various types of functions available in SQL Single row functions return a result for every row in a result. They can be used in any part of a SQL query, such as the SELECT lists or any part that can use a conditional statement. Oracle 11g has […]

Handling Nulls

Randy Sims Oracle

Null is an unknown value. Null is Schrodinger’s Cat’s state before opening the box. There are ways to deal with null that are unlike anything else in the database, both in concept and in function.

Null Parameters in WHERE Conditions

Randy Sims Oracle

When building a procedure, I want to be able to pass a parameter or pass a NULL if I don’t want to search by a parameter. Some people will tell you to accomplish this that you should write a dynamic SQL statement so you can add or remove parameters as needed. I avoid Dynamic SQL […]