SQL functions

This list includes functions currently supported by Network Reporting.

abs

Absolute value.

ABS(number)
SELECT ABS('-17')
FROM hcp
LIMIT 1;
SELECT ABS(latitude__v)
FROM address;
Input

-17  -37.845

Result

17  37.845

avg

Average (arithmetic mean) of all input values.

AVG(expression)

SELECT AVG(birth_year__v) FROM hcp

btrim

Removes the longest substring in matching_string consisting only of characters in string (a space by default) from the start and end of string.

BTRIM( string [, maching_string] )

SELECT BTRIM(formatted_name__v, 'Doctor') FROM hcp

Input

Doctor Janet Smith

Result

Janet Smith

cast

A type cast specifies a conversion from one data type to another.

CAST ( expression AS type )

SELECT CAST(created_date__v AS char(20)) FROM hcp

ceil/ceiling

Returns the smallest integer equal to or larger than the input argument.

CEIL | CEILING(number)

SELECT CEIL(latitude__v) FROM address

Input

39.45

Result

40

charindex

Returns the numeric position of a substring within the specified string.

CHARINDEX(substring, string)

SELECT corporate_name__v FROM hco WHERE CHARINDEX('Clinic', corporate_name__v) > 0

Input

Bridge Clinic

Result

7

chr

Returns the character matching the specified ASCII value.

CHR(number)

SELECT DISTINCT corporate_name__v FROM hco WHERE SUBSTRING(corporate_name__v, 1, 1) = CHR(65)

Input

-

Result

Returns all corporate names which start with a capital "A"

coalesce (or nvl)

Returns the first of its arguments that is not null. Null is returned only if all arguments are null. This is often useful to substitute a default value for null values when data is retrieved for display.

COALESCE | NVL(expression, expression, ...)

SELECT COALESCE(medical_degree_1__v, 'No Degree') FROM hcp

Input

RN  medical_degree_1__v IS NULL

Result

o Degree

concat (or ||)

Concatenates two character strings.

CONCAT (string1, string2) string1 || string2 || string3 || ...

SELECT CONCAT(first_name__v, last_name__v) AS "Name" FROM hcp; SELECT first_name__v || ' ' || last_name__v AS "Name" FROM hcp;

Input

John Smith   John Smith

Result

JohnSmith  John Smith

convert

Specifies a conversion from one data type to another.

CONVERT(type, expression)

SELECT CONVERT(integer, npi_num__v)
FROM hcp

convert_timezone

Converts a time from one time zone to another.

CONVERT_TIMEZONE ( ['source_timezone',] 'target_timezone', 'timestamp')

SELECT created_date__v, CONVERT_TIMEZONE ('EST', created_date__v) FROM hcp

Input

2013-10-28 5:18:12

Result

2013-10-28 4:18:12

count

Returns the number of input values for which the value of expression is not null.

COUNT(expression)

SELECT COUNT(*) FROM hcp WHERE hcp_type__v = 'D'

Input

-

Result

4,800

current_date

Returns a date in the current session time zone in the format YYYY-MM-DD.

CURRENT_DATE

SELECT CURRENT_DATE FROM hcp LIMIT 1

Input

-

Result

2017-10-06

dateadd

Increments a date or time stamp by the specified interval.

DATEADD( datepart, interval, {date|timestamp} )

SELECT modified_date__v, DATEADD('day',30, modified_date__v) FROM hcp

Input

2017-05-09 17:36:03

Result

2017-06-08 21:36:03

date_cmp

Compares two dates, returning an integer. Identical dates return 0. If the first date is greater, 1 is returned. If the second date is greater, -1 is returned.

DATE_CMP(date1, date2)

SELECT DATE_CMP(expiration_date__v, effective_date__v) FROM license

Input

2017-10-06, 2017-10-07  2017-10-06, 2017-10-06  2017-10-07, 2017-10-06

Result

-1   0   1

datediff

Returns the difference between parts of two date or time expressions.

DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} )

SELECT created_date__v, modified_date__v, DATEDIFF('day',created_date__v, modified_date__v) AS "Difference in Days" FROM hcp

Input

2017-10-05, 2017-10-09

Result

4

date_part

Extracts part of a date from a date or time stamp.

DATE_PART ( datepart, {date|timestamp} )

SELECT DATE_PART('day', created_date__v) FROM hcp

Input

2017-10-16

Result

16

date_trunc

Truncates a time stamp on the date part specified, such as hour, day, week, or month.

DATE_TRUNC('datepart', timestamp)

SELECT DATE_TRUNC('day', created_date__v) FROM hcp

Input

2015-02-19

Result

2015-02-20

exp

Executes the natural exponential function (base e) to the power of the numeric expression provided. Returns the exponential value in scientific notation for a numeric expression.

EXP( expression )

SELECT EXP(1) FROM hcp LIMIT 1

Input

1

Result

2.718281828

extract

Returns part of a date (day, month, year) from a time stamp.

EXTRACT ( datepart FROM { TIMESTAMP 'literal' | timestamp } )

SELECT EXTRACT( 'month' from created_date__v )
FROM hcp

Input

2017-10-06 12:00:00

Result

10

floor

Returns the largest integer that is not greater than the argument.

FLOOR(number)

SELECT FLOOR(latitude__v) FROM address

Input

42.56

Result

42

getdate

Retrieves the current date and time in the time zone of the current instance.

GETDATE ()

SELECT GETDATE() FROM hcp LIMIT 1

Input

-

Result

2017-10-06 10:23:00

last_day

Returns the date of the last day of the month that contains the specified date value.

LAST_DAY ( { date | timestamp } )

SELECT DISTINCT LAST_DAY(created_date__v) FROM hco

Input

2017-10-06 0:00:00

Result

2017-10-31

len/length

Returns the length of the specified string as the number of characters.

LEN (expression)

SELECT corporate_name__v, LEN(corporate_name__v) FROM hco

Input

St. Michael's Hospital

Result

22

listagg

Concatenates values into a single string, by the provided delimiter. Using the DISTINCT argument, you can eliminate duplicate values from the specified expression before concatenating the values into a single string.

LISTAGG( [DISTINCT] aggregate_expression) [, 'delimiter'])

SELECT LISTAGG(DISTINCT hcp_type__v, ', ') FROM hcp

Input

-

Result

N, P, A, S, O, R, D, T, NU, PH, DT

Supports the WITHIN GROUP (ORDER BY <list_type>) clause so you can specify the sort order of the aggregated values.

LISTAGG( [DISTINCT] aggregate_expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ]

SELECT LISTAGG(DISTINCT hcp_type__v, ', ') WITHIN GROUP (ORDER BY hcp_type__v) FROM hcp

Input

-

Result

The results are sorted alphabetically by HCP type.

A, D, DT, N, NU, O, P, PH, R, S, T

ln

Natural logarithm. 

LN(expression)

SELECT LN(2.718281828) FROM hco LIMIT 1

Input

2.718281828

Result

0.9999999998

log

Returns the base 10 logarithm of a number.

LOG(number)

SELECT LOG(10) FROM hco LIMIT 1

Input

10

Result

1

lower

Converts a string to lower case.

LOWER(string)

SELECT formatted_name__v, LOWER(formatted_name__v) FROM hcp

Input

John Smith

Result

john smith

lpad

Prepends specified characters to a string up to the specified length. If the original string is longer than the specified length, the string is truncated to that length. The default fill character is a blank.

LPAD (string1, length, [ string2 ])

SELECT LPAD(formatted_name__v, 15) FROM hcp

Input

Doctor Johnny Lee

Result

Doctor Johnny L

ltrim

Trims a specified set of characters from the beginning of a string.

LTRIM( string, 'trim_chars' )

SELECT LTRIM(formatted_name__v, 'Nurse') FROM hcp

Input

Nurse Sally Smith

Result

Sally Smith

max

Maximum value of an expression across all input values.

MAX(expression)

SELECT MAX(count_beds__v), hco_type__v FROM hco GROUP BY hco_type__v

Input

-

Result

2487, Hospital

md5

Calculates the MD5 hash of string, returning the result in hexadecimal.

MD5(string)

SELECT MD5(vid__v) FROM hcp

Input

590876973887229953

Result

06bf45d7227179caea85735c6a345563

median

Calculates a median value from a range of values.

MEDIAN ( median_expression )

SELECT hcp_type__v,
MEDIAN( birth_year__v )
FROM hcp
GROUP BY hcp_type__v

min

Minimum value of an expression across all input values.

MIN(expression)

SELECT MIN(count_beds__v), hco_type__v FROM hco GROUP BY hco_type__v

Input

2

Result

Clinic

mod

Remainder of two numeric values; the first is divided by the second.

mod(number1, number2)

SELECT MOD(17,5) FROM hco LIMIT 1

Input

17, 5

Result

2

next_day

Returns the date of the first instance of a specified day that is later than the specified date.

NEXT_DAY ( { date | timestamp }, day )

SELECT created_date__v, NEXT_DAY(created_date__v, 'Tuesday') FROM hcp

Input

2017-10-06

Result

2017-10-10

position

Returns a substring within a string.

POSITION(<search-substring> IN <source-string> )

SELECT formatted_name__v,
POSITION('Doctor' IN formatted_name__v)
FROM hcp

Input

Sally Smith,

Doctor John Smith

Result

0, 1

power

A specified value raised to the power of another specified value.

POWER(<base>, <exponent>)

SELECT POWER(10,2) FROM hco LIMIT 1

Input

10

Result

100

random

Random value between 0.0 and 1.0.

RANDOM()

SELECT RANDOM() FROM hco LIMIT 1

Input

0.3787931639

Result

-

round

Round a number to the nearest decimal or integer.

ROUND (decimal, scale)

SELECT longitude__v, ROUND(longitude__v, 3) FROM address

Input

10.372864

Result

10.373

regexp_count

Searches a string for a regex pattern and returns a value indicating the number of times the pattern occurs in the string. If no match is found, the function returns 0. 

REGEXP_COUNT ( source_string, pattern [, position ] )

SELECT email_1__v, REGEXP_COUNT(email_1__v, '.com') FROM hcp WHERE email_1__v IS NOT NULL

Input

bob.smith@veeva.com  mary.lou@hcp.org

Result

1  0

regexp_instr

Searches a string for a regex pattern and returns a a value indicating the beginning position of the matched substring. If no match is found, the function returns 0. 

REGEXP_INSTR ( source_string, pattern [, position ] )

SELECT email_1__v, REGEXP_INSTR(email_1__v, '@') FROM hcp WHERE email_1__v IS NOT NULL

Input

bob.smith@veeva.com

Result

10

regexp_replace

Searches a string for a regex pattern and replaces every occurrence of the pattern with the specified string.

REGEXP_REPLACE ( source_string, pattern [, replace_string [ , position ] ] )

SELECT email_1__v, REGEXP_REPLACE(email_1__v, '@.*\.(org|gov|com|edu|net|ORG|GOV|COM|EDU|NET)$') FROM hcp WHERE email_1__v IS NOT NULL

Input

bob.smith@veeva.com

Result

bob.smith

regexp_substr

Returns the characters extracted from a string by searching for a regex pattern.

REGEXP_SUBSTR ( source_string, pattern [, position ] )

SELECT email_1__v, REGEXP_SUBSTR (email_1__v, '@.*\.(org|gov|com|edu|ORG|GOV|COM|EDU)$') AS "Domain Extension" FROM hcp WHERE email_1__v IS NOT NULL GROUP BY email_1__v ORDER BY email_1__v ASC

Input

bob.smith@veeva.com

Result

eeva.com

repeat

Repeats a string the specified number of times. If the input parameter is numeric, it is treated as a string.

REPEAT(string, integer)

SELECT phone_1__v, REPEAT(phone_1__v, 2) FROM address

Input

416-555-4444

Result

416-555-444416-555-4444

replace

Replaces all occurrences of a set of characters within an existing string with another set of specified characters.

REPLACE(string1, old_chars, new_chars)

SELECT formatted_name__v, REPLACE(formatted_name__v, 'Doctor', 'M.D.') FROM hcp WHERE formatted_name__v LIKE '%Doctor'

Input

Kelly Smith Doctor

Result

Kelly Smith M.D.

reverse

Returns the characters of a string in reverse order. This function works on numeric and date data types as well as the character data type.

REVERSE ( expression )

SELECT REVERSE(corporate_name__v) FROM hco

Input

Diabetes Australia Act

Result

tcA ailartsuA setebaiD

rpad

Appends specified characters to a string up to the specified length. If the original string is longer than the specified length, the string is truncated to that length. The default fill character is a blank.

RPAD (string1, length, [ string2 ])

SELECT RPAD(formatted_name__v,20,'0123456789') FROM hcp

Input

Michael Smith

Result

Michael Smith0123456

rtrim

Trims a specified set of characters from the end of a string.

RTRIM( string, trim_chars )

SELECT RTRIM(corporate_name__v, 'LTD.') FROM HCO

Input

Veeva Systems LTD.

Result

Veeva Systems

split_part

Splits a string on the specified delimiter and return part based on a specified position.

SPLIT_PART(string, delimiter, part)

SELECT expiration_date__v, SPLIT_PART(expiration_date__v,'-',1) AS YEAR, SPLIT_PART(expiration_date__v,'-',2) AS MONTH, SPLIT_PART(expiration_date__v,'-',3) AS DAY FROM license

Input

2017-10-06

Result

2017,10,6

sqrt

Returns the square root for the specified value.

SQRT(expression)

SELECT SQRT(25) FROM hco LIMIT 1

Input

25

Result

5

strpos

Returns the position of a substring within a specified string.

STRPOS(string, substring )

SELECT STRPOS(formatted_name__v, 'Doctor') FROM hcp

Input

Doctor Johnny Lee  Nurse Michelle Smith

Result

1  0

substring

Returns a subset of characters extracted from a string based on specified characters and position elements.

SUBSTRING(string, start_position, number_characters )

SELECT corporate_name__v, SUBSTRING(corporate_name__v, 10, 50) FROM hco WHERE corporate_name__v LIKE 'Corporate%'

Input

Corporate Health Associates

Result

Health Associates

sum

Sum of an expression across all input values.

SUM ( [ DISTINCT | ALL ] expression )

SELECT hcp_type__v, SUM(hcp_fee__v) FROM hcp GROUP BY hcpy_type__v

timeofday

Returns the current date and time.

TIMEOFDAY()

SELECT TIMEOFDAY() FROM hcp LIMIT 1

Input

-

Result

Fri Oct 06 12:46:33.819503 2017 PDT

timestamp_cmp

Compares two time stamps, returning an integer. Identical time stamps return 0. If the first time stamp is greater, 1 is returned. If the second time stamp is greater, -1 is returned.

TIMESTAMP_CMP(timestamp1, timestamp2)

SELECT created_date__v, modified_date__v, TIMESTAMP_CMP (created_date__v, modified_date__v) FROM hco

Input

2017-10-06 12:00:00,2017-10-06 13:01:00  2017-10-06 12:00:00,2017-10-06 12:00:00  2017-10-06 13:00:00,2017-10-06 12:00:00

Result

-1  0  1

timestamptz_cmp

Compares of two time stamps including time zones, returning an integer. Identical time stamps return 0. If the first time stamp is greater, 1 is returned. If the second time stamp is greater, -1 is returned.

TIMESTAMPTZ_CMP(timestamptz1, timestamptz2)

SELECT created_date__v, modified_date__v, TIMESTAMPTZ_CMP (created_date__v, modified_date__v) FROM hco

Input

2017-10-06 12:00:00,2017-10-06 13:01:00  2017-10-06 12:00:00,2017-10-06 12:00:00  2017-10-06 13:00:00,2017-10-06 12:00:00

Result

-1  0  1

to_char

Converts a time stamp to a string.

TO_CHAR (timestamp_expression | numeric_expression , 'format')

SELECT created_date__v, TO_CHAR(created_date__v, 'MON-DD-YYYY HH12:MIPM') FROM hcp

Input

2016-01-12 18:12:59

Result

Jan-12-2016 18:12

to_date

Converts a string to a date.

TO_DATE (string, format)

SELECT TO_DATE('05 Dec 2000', 'DD Mon YYYY') FROM hcp LIMIT 1

Input

05 Dec 2000

Result

2000-12-05

to_number

Converts a string to a numeric.

TO_NUMBER (string, format)

SELECT TO_NUMBER('12.345','99D999') FROM hcp LIMIT 1

Input

12.345

Result

12.345

to_timestamp

Converts a string to a time stamp.

TO_TIMESTAMP ('timestamp', 'format')

SELECT TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY') FROM hcp LIMIT 1

Input

2015-04-24 12:34:51

Result

2000-12-05 0:00:00

trunc

Truncates a number and right-fills it with zeros from the position (integer) specified. If no position is specified, the number is truncated as a whole number. This function also truncates a timestamp and returns a date.

TRUNC(number [ , integer ] |
 timestamp )

SELECT TRUNC(42.86) FROM hcp; SELECT TRUNC(42.86, 1) FROM hcp;

Input

-

Result

42  42.8

upper

Converts a string to uppercase.

UPPER(string)

SELECT formatted_name__v, UPPER(formatted_name__v) FROM hcp

Input

John Smith

Result

JOHN SMITH