Supported SQL functions

since https //deselect com/deselect release january 21 sql functions/ , 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 returnsan 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