XLeratorDB/strings is now available
May
8
Written by:
Charles Flock
5/8/2009 11:55 AM
Some notes on the release of the strings package and how it works in relation to EXCEL and to some existing built-in SQL functions.
We are pleased to announce the availability of the XLeratorDB/strings module. With this package you will be able to replicate all the EXCEL text functions in SQL Server, plus there a few that even EXCEL doesn’t do. There are 36 new functions in all.
The strings module contains 10 functions to convert numbers to strings or strings to numbers: DEC2FRAC, which converts a floating point value to a fraction; DOLLAR converts a number to text using currency format; FIXED formats a number in decimal format using a period and commas; FORMATDATE, which formats a datetime value into a date using the .NET framework specifiers; FORMATNUM, which formats a floating point value into a text string using the .NET framework specifiers; FRAC2DEC, which converts a string that looks like a fraction into a floating point value; ISNUMERIC, which evaluates whether or not a string can be converted into a number and has a much wider definition than the built-in function; PCT2FRAC, which converts a floating point value into a percentage expressed as a proper fraction; TEXT, which converts a floating point value to a text string using the EXCEL specifiers; and VALUE, which converts any text string that looks like a number to a floating point value.
There are 11 functions that improve your ability to search a string: CONTAINSSTR returns a TRUE value if the string contains the value you are searching for; COUNTSTRING counts the number of times a particular text occurs in another text; ENDSWITH determines if a string ends with a particular value; EXACT compares two text strings; FIND locates one text string within a second text string; INSTR returns an integer specifying the position of the first occurrence of one string within another; INSTRREV returns an integer specifying the position of an occurrence of one string within another, from the end of string; ISALPHA determines if a value contains only alphabetic characters [a-z][A-Z]; NUMWORDS calculates the number of words in a string; PARSE finds a word in a string based on its word position in the string; and STARTSWITH determines if a string starts with a particular value.
There are 14 functions that actually manipulate the text data, apart from number formatting. CLEAN remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) and values 127, 129, 141, 143, 144 and 157 from text; CONCAT concatenates the contents of 2 columns; CRLF inserts a carriage return, CHAR(13), line feed CHAR(10), into a string; INDENT places characters at the beginning of a string of text; JOINSTR and JOINSTR_q concatenate the text from multiple rows in a table; PADLEFT places characters at the beginning of a string of text when the length of the text is less than the specified width; PADRIGHT places characters at the end of a string of text, when the length of the text is less than the specified width; PROPER capitalizes the first letter in each word of a string of text; QUOTED adds quotation marks, CHAR(34), to the beginning and end of a string of text; QUOTES adds quotation marks, CHAR(39), to the beginning and end of a string of text; STRIP removes punctuation from the beginning and end of a string; SUBSTITUTE substitutes new text for old ext in a text string; and TRIM removes trailing and leading spaces from a string of text.
Finally, there is the IIFSTR function which returns one of two parts, depending on the evaluation of an expression.
Where there were comparable functions in EXCEL, we tested by using the worksheetfunction objects and running millions of cases through those objects and through XLeratorDB. We did the same with comparable .NET framework functions. We ran over 500 million tests before we were satisfied with the results.
As with all the other XLeratorDB packages, you can use these functions on an unlimited number of databases with an unlimited number of users in a single instance of SQL Server.
We think that the string functions in this package make SQL Server a much more powerful tool. We hope that you find it a satisfying experience. If you have any comments, please post them on the forum, and if you have any questions or problems you can send us an e-mail at support@westclintech.com or call us at 914 231 7137.