Login     Register

        Contact Us     Search

XLeratorDB/strings Online Documentation

SQL Server string functions


XLeratorDB / strings

Use XLeratorDB / strings for a wide variety of string processing and text manipulations. The feature-rich XLeratorDB function library lets you include calculations in any T-SQL statement including SELECT, INSERT, UPDATE, DELETE, CREATE VIEW as well as in CTEs, stored procedures, user-defined functions, and computed columns.

The XLeratorDB / strings functions requires Microsoft SQL Server® 2005 or later (which must be purchased separately). XLeratorDB/strings installs in minutes and can be used immediately after installation. Anyone familiar with the string functions in Microsoft Excel® will immediately feel comfortable with XLeratorDB/strings.

FUNCTION REFERENCE - STRING FUNCTIONS
FORMATTING
CLEAN
 
Remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text.
 
CONCAT
 
Concatenate the contents of 2 columns.
 
CRLF
 
Insert a carriage return (Char(13)) line feed (Char(10)) into a string.
 
DEC2FRAC
 
Convert a number into its fractional representation.
 
DOLLAR
 
Convert a number to text using currency format, with the decimals rounded to the specified place.
 
FIXED
 
Format a number in decimal format using a period and commas, round the number to the specified number of decimals, and return the result as text.
 
FORMATDATE
 
Convert a datetime value to text and specify the display formatting by using special format strings.
 
FORMATNUM
 
Convert a numeric value to text and specify the display formatting by using special format strings.
 
FRAC2DEC
 
Convert a fraction, as text, to a numeric value.
 
INDENT
 
Place characters at the beginning of a string of text.
 
JOINSTR
 
Concatenate the text from multiple rows in a table. 
 
JOINSTR_q
 
Concatenate the text from multiple rows in a table. 
 
PADLEFT
 
Place characters at the beginning of a string of text, when the length of the text is less than the specified width.
 
PADRIGHT
 
Place characters at the end of a string of text, when the length of the text is less than the specified width.
 
PARSE
 
Find a word in a string based on its word position in the string.
 
PCT2FRAC
 
Convert a percentage into its fractional representation.
 
PROPER
 
Capitalize the first letter in each word of a string of text.
 
QUOTED
 
Add quotation marks (CHAR(34)) to the beginning and end of a string of text.
 
QUOTES
 
Add quotation marks (CHAR(39)) to the beginning and end of a string of text.
 
SPLIT
 
Generate a single column table containing the substrings between the specified delimiter passed to the function.
 
STRIP
 
Remove punctuation from the beginning and end of a string.
 
SUBSTITUTE
 
Substitute new_text for old_text in a text string.
 
TEXT
 
Convert a numeric value to text and specify the display formatting by using special format strings.
 
TRIM
 
Remove trailing and leading spaces from a string of text.
 
VALUE
 
Convert a text string that represents a number to a number.
 
 
INSPECTION
CONTAINSSTR
 
Determine if a string contains a particular value.
 
COUNTSTRING
 
Count the number of times a particular text occurs in another text.
 
ENDSWITH
 
Determine if a string ends with a particular value.
 
EXACT
 
Compare two text strings.
 
FIND
 
Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
 
INSTR
 
Return an integer specifying the position of the first occurrence of one string within another.
 
INSTRREV
 
Return an integer specifying the position of an occurrence of one string within another, from the end of string.
 
ISALPHA
 
Determine if a value contains only alphabetic characters [a-z][A-Z].
 
ISNUMERIC
 
Determine if a value is numeric.
 
NUMWORDS
 
Calculate the number of words in a string.
 
STARTSWITH
 
Determine if a string starts with a particular value.
 
 
CALENDAR
NETWORKDAYS
 
Calculate the number of whole working days between a start date and an end date. 
 
NETWORKDAYS_q
 
Calculate the number of whole working days between a start date and an end date. 
 
WORKDAY
 
Calculate the date that is the indicated number of working days before or after a date (the starting date). 
 
WORKDAY_q
 
Calculate the date that is the indicated number of working days before or after a date (the starting date). 
 
 
LOGIC
IIFSTR
 
Return one of two parts, depending on the evaluation of an expression.
 
 
OTHER
XLDB_STRINGS_VERSION
 
Get the XLeratorDB / strings version information.
 
 
SAMPLE DATA
Gettysburg Address
 
Sample data
 
 

This function cannot reference data from SQL Server 2014 memory-optimized tables


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service