# R Functions and Operators in SQL Server

By: Matteo Lorini | Updated: 2020-09-23 | Comments (3) | Related: More > R Language

##### Problem

I have a basic knowledge of R, but what are the main operators and functions that I need to be familiar with to start R development?

##### Solution

R exposes a range of powerful functions. In this article, we will examine a few of the main and most used functions.

## Help for R Functions

The Help function is the most common and important function in R. For example, if
we are unsure what R function **str** does we can execute the following code
to display the online manual page of *str*.

# Help ? str

## Basic R Operators and Assignments

R has several assignments and operators like: **%in%, match, =, <-, <<-,
$, [, [[, head, tail, subset, with, assign and get.**

In this section; we will focus on two (%in% and match) that I think are less intuitive.

The %in% operator in R, is used to identify if an element belongs to a vector. In our example, element a1 belongs to vector t, %in% operator will return TRUE, and it will return FALSE on a2 because a2 does not belong to t.

a1 <- 13 a2 <- 76 t <- c(1,2,3,4,5,6,7,8,13,20,77) print(a1 %in% t) print(a2 %in% t)

Example using R in SQL Server Management Studio:

DECLARE @rscript NVARCHAR(MAX); SET @rscript = N' # R %in% operator a1 <- 13 a2 <- 76 t <- c(1,2,3,4,5,6,7,8,13,20,77) print(a1 %in% t) print(a2 %in% t) '; EXEC sp_execute_external_script @language = N'R', @script = @rscript; GO

The match() function returns a vector of the position of first occurrence of the vector1 in vector2. If the element of the vector1 does not exist in vector2, NA is returned. Let's see it with an example:

# Return position of 55 print(match(55, c(51,52,59,55,53,56,57,54,55))) # Return the position of each element ib v1 that are in v2 v1 <- c("M1","S2","Q1","L2") v2 <- c("g1","L2","d2","e2","f1","M1","c2","S2","a2") x <- match(v1,v2) print(x)

Example using R in SQL Server Management Studio:

DECLARE @rscript NVARCHAR(MAX); SET @rscript = N' # Return position of element 55 print(match(55, c(51,52,59,55,53,56,57,54,55))) # Return the position of each element in v1 that are in v2 v1 <- c("M1","S2","Q1","L2") v2 <- c("g1","L2","d2","e2","f1","M1","c2","S2","a2") x <- match(v1,v2) print(x) '; EXEC sp_execute_external_script @language = N'R', @script = @rscript; GO

## R Comparison functions

R has several comparison
operators like: **
all.equal,
identical, !=, ==, >, >=, <, <=, is.na, complete.cases and is.finite.**

In this section we will examine the following: **all.equal,
identical, is.na, complete.cases and is.finite.**

# Identical and all.equal t1 <- c(1,2,3,4,5,6,7,8,13,20,77) t2 <- c(1,2,3,4,5,6,7,8,13,20,77) print(all.equal(t1, t2)) print(identical(t1,t2)) t1 <- c(1,2,3,4,5,6,7,8,13,20,77) t2 <- c(1,2,4,3,5,6,7,8,13,20,77) print(all.equal(t1, t2)) print(identical(t1,t2))

Example using R in SQL Server Management Studio.

DECLARE @rscript NVARCHAR(MAX); SET @rscript = N' # Identical and all.equal t1 <- c(1,2,3,4,5,6,7,8,13,20,77) t2 <- c(1,2,3,4,5,6,7,8,13,20,77) print(all.equal(t1, t2)) print(identical(t1,t2)) t1 <- c(1,2,3,4,5,6,7,8,13,20,77) t2 <- c(1,2,4,3,5,6,7,8,13,20,77) print(all.equal(t1, t2)) print(identical(t1,t2)) '; EXEC sp_execute_external_script @language = N'R', @script = @rscript; GO

The **complete.cases**
function is often used to identify complete rows of a data frame. In the following
example we will show how to use **complete.cases** and **is.na**
functions.

# Create a data frame with incomplete data data <- data.frame(x1 = c(7, 2, 1, NA, 9), x2 = c(1, 3, 1, 9, NA), x3 = c(NA, 8, 8, NA, 5)) # This is how our example data looks like print(data) # This is the return values of complete.cases function print(complete.cases(data)) # substitute NA values with -1 value data[is.na(data)] <- -1 # This is how our example data looks like print(data) # This is the return values of complete.cases function print(complete.cases(data))

Example using R in SQL Server Management Studio.

DECLARE @rscript NVARCHAR(MAX); SET @rscript = N' # Create a data frame with incomplete data data <- data.frame(x1 = c(7, 2, 1, NA, 9), x2 = c(1, 3, 1, 9, NA), x3 = c(NA, 8, 8, NA, 5)) # This is how our example data looks like print(data) # This is the return values of complete.cases function print(complete.cases(data)) # substitue NA values with -1 value data[is.na(data)] <- -1 # This is how our example data looks like print(data) # This is the return values of complete.cases function print(complete.cases(data)) '; EXEC sp_execute_external_script @language = N'R', @script = @rscript; GO

is.finite and is.infinite return a vector of the same length indicating which elements are finite and which one are not.

t1 <- c(1,2,3) is.finite(t1) t2 <- c(1,NA,3) is.finite(t2)

Example using R in SQL Server Management Studio:

DECLARE @rscript NVARCHAR(MAX); SET @rscript = N' t1 <- c(1,2,3) print(t1) print(is.finite(t1)) t2 <- c(1,NA,3) print(t2) print(is.finite(t2)) '; EXEC sp_execute_external_script @language = N'R', @script = @rscript; GO

## Basic R math operators

R has many math
operators, for example the most common are: ***, +, -, /,
^, %%, %/%, abs, sign, cacos, asin, atan, atan2, csin, cos, tan, ceiling, floor, round, trunc, signif, exp, log, log10,
log2, sqrt, max, min, prod, sum, cummax, cummin, cumprod, cumsum, diff, pmax, pmin, crange, cmean,
median, cor, sd, var rle.**

The goal of this article is not
to teach all of the operators, but to show how we can understand and work with them.
For example, suppose that we are not familiar with **rle** operator. First of all, we can execute the help on such operator:

? rle

Let's see how it works with an example:

x <- c(1,2,2,2,5,6,2,8,8,10,10,10) print(x) print(rle(x))

Example using R in SQL Server Management Studio:

DECLARE @rscript NVARCHAR(MAX); SET @rscript = N' x <- c(1,2,2,2,5,6,2,8,8,10,10,10) print(x) print(rle(x)) '; EXEC sp_execute_external_script @language = N'R', @script = @rscript; GO

## R Logical and Sets operators

R has several comparison operators like: **
&, |, !,
xor, all, any, intersect, union, setdiff, setequal and which.**

In this section we will examine the following:**
setdiff, setequal and which.**

*The* **setdiff** function operates row-wise on lists and dataframes. The elements
of setdiff(x,y) returns elements in X but not in** **Y. Let's see
it with an example:

# Create two sample lists x <- c(1,2,2,2,5,6,2,8,8,10) y <- c(1,2,2,3,5,6,2,8,8,0,9) # Compare X to Y and print any differences print(setdiff(x,y)) # Compare Y to X and print any differences print(setdiff(y,x))

Example using R in SQL Server Management Studio:

DECLARE @rscript NVARCHAR(MAX); SET @rscript = N' # Create two sample lists x <- c(1,2,2,2,5,6,2,8,8,10) y <- c(1,2,2,3,5,6,2,8,8,0,9) # Compare X to Y and print any differences print(setdiff(x,y)) # Compare Y to X and print any differences print(setdiff(y,x)) '; EXEC sp_execute_external_script @language = N'R', @script = @rscript; GO

**Setequal(x,y)** compares if the elements in X are the same of the element
of y. Element order does not matter.

# Create two sample lists x <- c(1,2,2,2,5,6,2,8,8,10) y <- c(1,2,2,3,5,6,2,8,8,0,9) # Check if they are equal print(setequal(x,y)) x1 <- c(1,9,2,2,2,5,6,2,8,8,10) y1 <- c(10,2,2,2,5,6,2,8,9,8,1) # Check if they are equal print(setequal(x1,y1))

Example using R in SQL Server Management Studio:

DECLARE @rscript NVARCHAR(MAX); SET @rscript = N' # Create two sample lists x <- c(1,2,2,2,5,6,2,8,8,10) y <- c(1,2,2,3,5,6,2,8,8,0,9) # Check if they are equal print(setequal(x,y)) x1 <- c(1,9,2,2,2,5,6,2,8,8,10) y1 <- c(10,2,2,2,5,6,2,8,9,8,1) # Check if they are equal print(setequal(x1,y1)) '; EXEC sp_execute_external_script @language = N'R', @script = @rscript; GO

Which indicates the elements in a list, matrix or data frame are true. Let's see it with an example.

# Create a numeric matrix ( m <- matrix(1:12, 3, 4) ) # Select all teh value that are divisible by 5 div.5 <- m %% 5 == 0 which(div.5) # Display the matrix coordinates of the values divisible by 5 which(div.5, arr.ind = TRUE)

Example using R in SQL Server Management Studio:

DECLARE @rscript NVARCHAR(MAX); SET @rscript = N' # Create a numeric matrix ( m <- matrix(1:12, 3, 4) ) print(m) # Select all teh value that are divisible by 5 div.5 <- m %% 5 == 0 print("Elements that are divisible by 5") print(which(div.5)) # Display the matrix coordinates of the values divisible by 5 print("Coordinate of Elements that are divisible by 5") print(which(div.5, arr.ind = TRUE)) '; EXEC sp_execute_external_script @language = N'R', @script = @rscript; GO

## Conclusion

In this tip we have learned a few of the many R operators and functions. In the next tip we will see how to work with functions and how to create new functions.

##### Next Steps

- The reader will need to install RStudio in order to test this tip.
- R Tutorial
- Check out these tips

Last Updated: 2020-09-23

##### About the author

**View all my tips**