SQL Functions

Introduction

These functions can be used when writing SQL statements for reports, Notifications, and variables Write Your Own. They simplify a lot of the work of formatting dates, SSN, phone numbers, etc. Some functions are used in other functions. For example, the FileExists functions can be used while researching if files linked with the system still exists.

How to use

Most of the formatting functions can be used inside a SQL statement on reports of SQL queries.

Functions

General

ConcatWs

Description: Concatenates values using a specified concatenator WS(varchar) - the concatenator, t1-t10 varchar(10) the values to be concatenated

Parameters: Concatenator varchar, T1-T10 Values to be concatenated

Returns: VARCHAR

Example: select dbo.ConcatWs('-',‘a’,‘b’,‘c’,’d',null,null,null,null,null,null)

Result: a-b-c-d

DirectoryHasCellPhone

Description: Check if a directory record has a cell phone number. (using a phone type that is set to accept text message in the phone type code table)

Parameters: @directory_id INT

Returns: int, 1-true, 0-false

DirectoryHasPhone

Description: Check if a directory record has a phone number.

Parameters: @directory_id INT

Returns: int, 1-true, 0-false

FileExists

Description: Check whether a file exists. When using this function users need to be sure SQL server is running under a service account that allows SQL to see the files. If SQL Server does not have rights to see files, this functions will always return Not Found
Parameters: filename including path
Returns: int 1- file found, 0 - file not found

FileNameWithoutPath

Description: Returns the name of a file without the path
Parameters: filename including path
Returns: varchar

iAge

Description: Return the number of years between 2 dates.
Parameters: @from DATETIME, @to DATETIME
Returns: INT
Example: select dbo.iage(‘5/20/1970’,‘5/27/2021’)
Result: 51

pageUrl

Description: Return STAC URL of a Case or Image to be used as a hyperlink on a Report.

Parameters: @pageType VARCHAR, @id INT

Returns: VARCHAR

Example: SELECT dbo.pageUrl('INCIDENT', 12345)

Page Types

Value	Description
INCIDENT	Return STAC URL for a case
DIRECTORY	Return STAC URL for an directory

Dates

GetNextBusinessDay

Description: Return the next business day from the date provided. This function reads the holiday table in addition to Saturdays and Sundays
Parameters: @date DATETIME
Returns: DATETTIME
Example: select dbo.GetNextBusinessDay(‘5/21/2021’)
Result: 2021-05-24

IsBusinessDay

Description: Indicates if a date is a business day. This function reads the holiday table
Parameters: @date DATETIME
Returns: INT
Example: select dbo.IsBusinessDay(‘5/27/21’)
Result: 1

Formatting

fmtAddress

Description: Format a address using the selected format.
Parameters: Format INT, address_1 VARCHAR, address_2 VARCHAR, city VARCHAR, state VARCHAR, zipcode VARCHAR, complement VARCHAR
Returns: VARCHAR

Type of Format

Format	Result
1	Return Full Address in one line
2	Return Full Address with break line

Examples

fmtCurrency

Description: Format a number as currency
Parameters: decimal DECIMAL, option INT
Returns: varchar
Example: select dbo.fmtCurrency(‘1234.56’,1)
Result: $1,234.56

Type of Options

Option	Description
0	Return amount formatted as currency (includes dolar sign)
1	Return amount formatted as currency (includes dolar sign), Null if amount=0
3	Return number formatted as decimal
4	Return number formatted as decimal, Null if amount=0
5	Return number formatted as percentual
6	Return number formatted as percentual, Null if amount=0

fmtDate

Description: Format a date
Parameters: value date-time
Returns: varchar date standard format
Example: select dbo.fmtDate(‘5/6/21’)
Result: 05/06/2021

fmtDateFirstOfMonth

Description: Return the first day of the month
Parameters: value date-time
Returns: varchar
Example: select dbo.fmtDateFirstOfMonth(‘5/6/21’)
Result: 05/01/2021

fmtDateLastOfMonth

Description: Returns the last day of the month
Parameters: value date-time
Returns: varchar
Example: select dbo.fmtDateLastOfMonth(‘5/6/21’)
Result: 05/31/2021

fmtDateTime

Description: Format a date and time
Parameters: value date-time
Returns: varchar date and time standard format
Example: select dbo.fmtDatetime(‘5/6/21 09:00’)
Result: 05/06/21 9:00 AM

fmtDuplicateResolver

Description: Removes the contents in a string that are between square brackets
Parameters: varchar
Returns: varchar

fmtName

Description: Format a name using the selected format (see list of formats at the end of the page). This function takes more parameters, but it does not have to run a SELECT statement. If users have the directory table or relationship view already listed on their statement, this function will be faster.
Parameters: Format int, last_name varchar, first_name varchar, middle_name varchar, title varchar, has alias int, directory_id int, alias_of_directory_id int, offender_type varchar, directory_warning varchar, directory_warning_2 varchar
Returns: varchar

Types of Format

Format	Description
0	Last, Suffix, first, middle, offender type, HA/AKA, directory warning 1, directory warning 2
1	First, Middle, Last, Suffix
2	First, Middle, Last (Initials only)
3	Title, First, Middle, Last, Suffix
4	Title, Last, Suffix, First, Middle
5	Title (description), First, Middle, Last, Suffix
6	Title (description), Last, Suffix, First, Middle
7	First, Middle Initial, Last, Suffix
8	Title, Last
9	Last, Suffix, First, Middle Initial
10	First, Last
99	Last, Suffix, First, Middle

fmtNameById

Description: Format a name using the selected format (see list of formats at the end of the page). This function only takes the directory Id and it performs a select statement to get all the fields. This is very handy if users statement does not include the directory table or the relationship view
Parameters: Format int, directory_id int
Returns: varchar
Example: select dbo.fmtNameById(1, 444)
Result: STEVEN M JONES

Types of Format

Format	Description
0	Last, Suffix, first, middle, offender type, HA/AKA, directory warning 1, directory warning 2
1	First, Middle, Last, Suffix
2	First, Middle, Last (Initials only)
3	Title, First, Middle, Last, Suffix
4	Title, Last, Suffix, First, Middle
5	Title (description), First, Middle, Last, Suffix
6	Title (description), Last, Suffix, First, Middle
7	First, Middle Initial, Last, Suffix
8	Title, Last
9	Last, Suffix, First, Middle Initial
10	First, Last
99	Last, Suffix, First, Middle

fmtPercentage

Description: Format a number as a percentual
Parameters: Value Decimal, Decimal places int, blank when zero int
Returns: varchar
Example: select dbo.fmtPercentage('.156',2,1)
Result: 15.60%

fmtPhoneNumber

Description: Format a phone number. The function removes any spaces, dashes, and parenthesis from the argument provided
Parameters: Phone varchar
Returns: varchar
Example: select dbo.fmtPhoneNumber(‘9048675309’)
Result: (904) 867-5309

fmtRomanNumber

Description: Format a number as a roman numeral
Parameters: value int
Returns: varchar
Example: select dbo.fmtRomanNumber(49)
Result: XLIX

fmtSentenceCase

Description: Format a string in sentence case
Parameters: value varchar
Returns: varchar
Example: select dbo.fmtSentenceCase(‘this is an example of sentence case’)
Result: This Is An Example Of Sentence Case

fmtSSN

Description: Format an SSN
Parameters: value varchar
Returns: varchar
Example: select dbo.fmtSSN(‘987654321’)
Result: 987-65-4321

fmtYesNo

Description: Format a Yes or No
Parameters: value varchar Returns varchar Returns Yes when Y is passed, and No when N is passed

fmtZipCode

Description: Format a zip code using 5 or 9 digits. The function removes any spaces and dashes from the argument provided
Parameters: value varchar
Returns: varchar
Example: select dbo.fmtZipCode(‘322238765’)
Result: 32223-8765