Since DESelect January '21 Release, users have the ability to transform their fields using SQL functions.
The SQL functions feature provides a user-friendly, guided way of easily changing the format of the fields inside your Data Extensions. This way, DESelect users have full flexibility on how they interfere with their data, without worrying about SQL syntax.
All the SQL functions supported by DESelect are listed in the table below:
| Name | Description |
|
TEXT
|
|
|
ASCII
|
Returns the ASCII value of the first character in a string expression.
|
|
CHAR
|
The CHAR() function returns the character based on the ASCII code.
|
|
CHARINDEX
|
The CHARINDEX() function searches for a substring in a string and returns the position. Start position number is optional.
|
|
CONCAT
|
The CONCAT() function adds two or more strings together.
|
|
DATALENGTH
|
The DATALENGTH() function returns the number of bytes used to represent an expression of any data type.
|
|
DIFFERENCE
|
The DIFFERENCE() function compares two SOUNDEX values and returns
an integer (the match for this two values) from 0 to 4 (the best match).
|
|
FORMAT
|
The FORMAT() function formats a value (date/time and number) with the specified format pattern (and an optional culture).
|
|
LEFT
|
The LEFT() function extracts a number of characters from a string (starting from left).
|
|
LEN
|
The LEN() function returns the length of a string.
|
|
LOWER
|
The LOWER() function converts a string to lower-case.
|
|
LTRIM
|
The LTRIM() function removes leading spaces from a string.
|
|
NCHAR
|
The NCHAR() function returns the Unicode character based on the number code.
|
|
PATINDEX
|
The PATINDEX() function returns the position of a pattern in a string. If the pattern is not found, this function returns 0.
|
|
QUOTENAME
|
The QUOTENAME() function returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier.
The quote_char is optional.
|
|
REPLACE
|
The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.
|
|
REPLICATE
|
The REPLICATE() function repeats a string a specified number of times.
|
|
REVERSE
|
The REVERSE() function reverses a string and returns the result.
|
|
RIGHT
|
The RIGHT() function extracts a number of characters from a string (starting from right).
|
|
RTRIM
|
The RTRIM() function removes trailing spaces from a string.
|
|
SOUNDEX
|
The SOUNDEX() function returns a four-character code to evaluate the similarity of two expressions.
|
|
SPACE
|
The SPACE() function returns a string of the specified number of space characters.
|
|
STR
|
The STR() function returns a number as a string. The length of the result string and the number of decimals to display are optional.
|
|
STUFF
|
The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position.
|
|
SUBSTRING
|
The SUBSTRING() function extracts some characters from a string.
|
|
UNICODE
|
The UNICODE() function returns an integer value (the Unicode value), for the first character of the string expression.
|
|
UPPER
|
The UPPER() function converts a string to upper-case.
|
|
NUMERIC
|
|
|
ABS
|
The ABS() function returns the absolute value of a number.
|
|
ACOS
|
The ACOS() function returns the arc cosine of a number. The specified number must be between -1 to 1.
|
|
ASIN
|
The ASIN() function returns the arc sine of a number. The specified number must be between -1 to 1.
|
|
ATAN
|
The ATAN() function returns the arc tangent of a number.
|
|
ATN2
|
The ATN2() function returns the arc tangent of two numbers.
|
|
AVG
|
The AVG() function returns the average value of a numeric expression (can be a field or a formula).
|
|
CEILING
|
The CEILING() function returns the smallest integer value that is larger than or equal to a number.
|
|
COUNT
|
The COUNT() function returns the number of records returned by a select query. The expression could be a field or string value.
|
|
COS
|
The COS() function returns the cosine of a number.
|
|
COT
|
The COT() function returns the cotangent of a number.
|
|
DEGREES
|
The DEGREES() function converts a value in radians to degrees.
|
|
EXP
|
The EXP() function returns e raised to the power of a specified number.
|
|
FLOOR
|
The FLOOR() function returns the largest integer value that is smaller than or equal to a number.
|
|
LOG
|
The LOG() function returns the the natural logarithm of a number or the logarithm of the number to the specified base (which is optional).
|
|
LOG10
|
The LOG10() function returns the natural logarithm of a number to base 10.
|
|
MAX
|
The MAX() function returns the maximum value of an expression (can be a field or a formula).
|
|
MIN
|
The MIN() function returns the minimum value of an expression (can be a field or a formula).
|
|
PI
|
The PI() function returns the value of PI.
|
|
POWER
|
The POWER() function returns the value of a number raised to the power of another number.
|
|
RADIANS
|
The RADIANS() function converts a degree value into radians.
|
|
RAND
|
The RAND() function returns a random number between 0 (inclusive) and 1 (exclusive).
The seed is optional and if passed, the function returns a repeatable sequence of random numbers.
|
|
ROUND
|
The ROUND() function rounds a number to a specified number of decimal places.
Operation is optional and if passed, the function truncates the result to the number of decimals.
|
|
SIGN
|
The SIGN() function returns the sign of a number (from -1 to 1).
|
|
SIN
|
The SIN() function returns the sine of a number.
|
|
SQRT
|
The SQRT() function returns the square root of a number.
|
|
SQUARE
|
The SQUARE() function returns the square of a number.
|
|
SUM
|
The SUM() function returns the sum of all the values in the numeric expression (can be a field or a formula).
|
|
TAN
|
The TAN() function returns the tangent of a number.
|
|
DATE
|
|
|
CURRENT_TIMESTAMP
|
The CURRENT_TIMESTAMP function returns the current date and time, in a \'YYYY-MM-DD hh:mm:ss.mmm\' format.
|
|
DATEADD
|
The DATEADD() function adds a time/date date_part to a date and then returns the date.
|
|
DATEDIFF
|
The DATEDIFF() function returns the difference between two dates.
|
|
DATEFROMPARTS
|
The DATEFROMPARTS() function returns a date from the specified parts (year, month, and day values)
|
|
DATENAME
|
The DATENAME() function returns a specified part of a date. This function returns the result as a string value.
|
|
DATEPART
|
The DATEPART() function returns a specified part of a date. This function returns the result as an integer value.
|
|
DAY
|
The DAY() function returns the day of the month (from 1 to 31) for a specified date.
|
|
GETDATE
|
The GETDATE() function returns the current database system date and time, in a YYYY-MM-DD hh:mm:ss.mmm format.
|
|
GETUTCDATE
|
The GETUTCDATE() function returns the current database system UTC date and time, in a YYYY-MM-DD hh:mm:ss.mmm format.
|
|
ISDATE
|
The ISDATE() function checks an expression and returns 1 if it is a valid date, otherwise 0.
|
|
MONTH
|
The MONTH() function returns the month part for a specified date (a number from 1 to 12).
|
|
SYSDATETIME
|
The SYSDATETIME() function returns the date and time of the computer where the SQL Server is running.
|
|
YEAR
|
The YEAR() function returns the year part for a specified date.
|
|
LOGICAL
|
|
|
COALESCE
|
The COALESCE() function returns the first non-null value in a list.
|
|
CONVERT
|
The CONVERT() function converts a value (of any type) into a specified datatype. The style used to convert between data types is optional.
|
|
IIF
|
The IIF() function returns a value if a condition is TRUE, or another value if a condition is FALSE.
|
|
ISNULL
|
The ISNULL() function returns a specified value if the expression is NULL.
|
|
ISNUMERIC
|
The ISNUMERIC() function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise, it returns 0.
|
|
NULLIF
|
The NULLIF() function returns NULL if two expressions are equal, otherwise, it returns the first expression.
|
|
SESSION_USER
|
The SESSION_USER function returns the name of the current user in the SQL Server database.
|
|
SESSIONPROPERTY
|
The SESSIONPROPERTY() function returns the session settings for a specified option.
|
|
SYSTEM_USER
|
The SYSTEM_USER function returns the login name for the current user.
|
|
USER_NAME
|
The USER_NAME() function returns the database user name based on the specified id.
If id is not specified, the function returns the name of the current user.
|
|
CASE STATEMENT
|
The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement).
So, once a condition is true, it will stop reading and return the result.
If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
|
Comments
0 comments
Please sign in to leave a comment.