<?xml version="1.0" encoding="UTF-8"?><!-- generator="WordPress/2.6.3" -->
<rss version="0.92">
<channel>
	<title>The ANSI SQL Blog</title>
	<link>http://www.computerbasedtraininginc.com/blog</link>
	<description>By Computer Based Training Inc.</description>
	<lastBuildDate>Wed, 07 Jan 2009 07:00:35 +0000</lastBuildDate>
	<docs>http://backend.userland.com/rss092</docs>
	<language>en</language>
	
	<item>
		<title>UPPER (chg. to upper case)</title>
		<description><![CDATA[The UPPER function changes all characters to lower case. Case changes affect only letters. Spaces, numbers, special characters, and punctuation remain unchanged.
Format: UPPER(string)
Example:           UPPER('What is THAT?')
Result: &#8216;WHAT IS THAT?&#8217;
&#62; Access does not support UPPER. Instead it uses UCASE.
&#62; DB2 and MySQL support UPPER as well [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2009/01/upper-chg-to-upper-case/</link>
			</item>
	<item>
		<title>TRIM (remove lead or trail chars.)</title>
		<description><![CDATA[The TRIM function removes leading characters, trailing characters, or both from a character string or BLOB value.
If a removal character is not specified, spaces are removed by default. TRIM will not remove characters within a string. The COLLATE clause forces the result set into another pre-existing collating sequence.
Format:
TRIM([[{LEADING&#124;TRAILING BOTH}] [remove_char] FROM source_string [COLLATE name])
Example:
TRIM(LEADING 'My [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2009/01/trim-remove-lead-or-trail-chars/</link>
			</item>
	<item>
		<title>TRANSLATE (chg. character set)</title>
		<description><![CDATA[The TRANSLATE function changes the representation of a character string from one base character set to another character set and collation.
Format: TRANSLATE(value, USING translation_char_name)
&#62; Access does not support TRANSLATE.
&#62; DB2 does not support the TRANSLATE standard. It has TRANSLATE(source, replacement, match).
&#62; MySQL does not support TRANSLATE. Instead, it uses the CONV function.
&#62; Oracle supports the [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2009/01/translate-chg-character-set/</link>
			</item>
	<item>
		<title>SUBSTRING (get part of string)</title>
		<description><![CDATA[The SUBSTRING function allows a shorter string to be extracted from a longer string. The result is extracted from the source_string, starting in position, start_position (an integer value), for the number of characters, length (an integer value, if specified). The COLLATE clause forces the result set into another pre-existing collating sequence.
Format: SUBSTRING(source_string FROM start_position [FOR [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2009/01/substring-get-part-of-string/</link>
			</item>
	<item>
		<title>OVERLAY (replace substring)</title>
		<description><![CDATA[The OVERLAY function changes all characters to lower case. The embedded_string replaces the length in string starting at character position start. If length is not specified, then the embedded_string will replace all characters after start in string.
Format: OVERLAY(string PLACING embedded_string FROM start [FOR length])
Example:           OVERLAY('Dare [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/overlay-replace-substring/</link>
			</item>
	<item>
		<title>LOWER (chg. to lower case)</title>
		<description><![CDATA[The LOWER function changes all characters to lower case. Case changes affect only letters. Spaces, numbers, special characters, and punctuation remain unchanged.
Format: LOWER(string)
Example:           LOWER('Look at THAT!')
Result: &#8216;look at that!&#8217;
&#62; Access does not support LOWER. Instead it uses LCASE.
&#62; DB2, and MySQL, support LOWER as well [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/lower-chg-to-lower-case/</link>
			</item>
	<item>
		<title>CONVERT (chg. to new chars.)</title>
		<description><![CDATA[The CONVERT function changes the representation of a character string within its character set.
 
Format: CONVERT(value, USING conversion_char_name)
 
&#62; Access supports CONVERT with a very different format.
&#62; Oracle supports CONVERT but the format is CONVERT(value, target_char_set, source_char_set).
&#62; PostgreSQL supports CONVERT and conversions can be defined using CREATE CONVERSION.
&#62; SQL Server&#8217;s CONVERT is very different and is similar [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/convert-chg-to-new-chars/</link>
			</item>
	<item>
		<title>Concatenate &#124;&#124; (attach values), Part 4 of 4</title>
		<description><![CDATA[If we have a table: ACCOUNTS_PAYABLE_TBL

Return VNDR_ID, INVN_ID, AMT_DUE, and then place &#8216;&#8211;&#62; &#8216; ahead of the NOTES and &#8216; &#60;&#8211;&#8217; behind the NOTES column.
          SELECT VNDR_ID,
                 INVN_ID,
   [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/concatenate-attach-values-part-4-of-4/</link>
			</item>
	<item>
		<title>Concatenate &#124;&#124; (attach values), Part 3 of 4</title>
		<description><![CDATA[If we have a table: VENDOR_TBL

If you concatenate the STATE and the COUNTRY columns, you get the following results:
          SELECT STATE &#124;&#124; COUNTRY
            FROM VENDOR_TBL;
Normally-defined character values are padded with spaces to the
length of the [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/concatenate-attach-values-part-3-of-4/</link>
			</item>
	<item>
		<title>Concatenate &#124;&#124; (attach values), Part 2 of 4</title>
		<description><![CDATA[If we have a table: VENDOR_TBL

The boss wants a list of vendor IDs (VNDR_ID) and
VENDOR_NAMEs from the VENDOR_TBL. But instead of
V001, V002, &#8230; he wants VENDOR001, VENDOR002, etc.
SELECT &#8216;VENDOR&#8217;
&#124;&#124; SUBSTRING(VNDR_ID, 2, 3),
VENDOR_NAME
FROM VENDOR_TBL;
The SUBSTRING function converts V001, V002, &#8230; to 001, 002, &#8230;
(substring from position 2 for a length of 3). Then the &#124;&#124; (concatenation) [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/concatenate-attach-values-part-2-of-4/</link>
			</item>
	<item>
		<title>Concatenate &#124;&#124; (attach values), Part 1 of 4</title>
		<description><![CDATA[Two alphanumeric character values (or strings) can be attached together (concatenated) to form one value
(or a single string). We do this by using the concatenation symbol &#124;&#124; which is two double-bar characters (two key strokes). Only string values can be concatenated.
Format: expression &#124;&#124; expression
Example:      'Concat' &#124;&#124; 'enation' -&#62; becomes -&#62; [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/concatenate-attach-values-part-1-of-4/</link>
			</item>
	<item>
		<title>WIDTH_BUCKET (get bucket value)</title>
		<description><![CDATA[The WIDTH BUCKET gives values to buckets in an equal-width histogram.
Format: WIDTH_BUCKET(expression, min, max, buckets)
Expression represents a value to be assigned to one of multiple buckets. This is normally applied to multiple row values. The range of values is defined by min (which is included in the buckets) and max (which is not included in [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/width_bucket-get-bucket-value/</link>
			</item>
	<item>
		<title>SQRT (square root of no.)</title>
		<description><![CDATA[he SQRT function returns the square root of a number.
Format: SQRT(expression)
Example:           SQRT(100)     Result: 10
                   SQRT(10000)   Result: 100
 
&#62; DB2, MySQL, Oracle, PostgreSQL, [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/sqrt-square-root-of-no/</link>
			</item>
	<item>
		<title>POWER (raise no. to power)</title>
		<description><![CDATA[The POWER function raises a number to a specific power or exponent.
Format: POWER(base, exponent)

Example:           POWER(10, 2) Result: 100
                   POWER(0, 0) Result: 1
      [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/power-raise-no-to-power/</link>
			</item>
	<item>
		<title>POSITION (start pos. of a string)</title>
		<description><![CDATA[The POSITION function returns an integer indicating the first starting position of a string within the search string.
If the search string is not found, zero will be returned.
Format: POSITION(search_string IN full_string)

Example:           POSITION('CADAB' IN 'ABRAH CADABRAH') Result: 7
         [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/position-start-pos-of-a-string/</link>
			</item>
	<item>
		<title>OCTET_LENGTH (bit length / 8)</title>
		<description><![CDATA[The OCTET_LENGTH function returns the number of octets (8 bits) within the string expression.
Format: OCTET_LENGTH(string)
Example:           SELECT CUST_NAME,
                          OCTET_LENGTH(CUST_NAME) AS 'OCTET_LENGTH'
 [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/octet_length-bit-length-8/</link>
			</item>
	<item>
		<title>MOD (division remainder)</title>
		<description><![CDATA[The MOD function returns the remainder of a dividend divided by a divisor.
Format:                        MOD(dividend, divisor)
Example:           SELECT MOD(10, 7) FROM TABLEA;
Result:
                    
&#62; Access, DB2, MySQL, PostgreSQL, SQL Server, and Sybase support the MOD function.
]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/mod-division-remainder/</link>
			</item>
	<item>
		<title>LN (natural logarithm)</title>
		<description><![CDATA[The LN function returns the natural logarithm of a number, which is the power to which you would need to raise the constant e (approximately 2.718281828&#8230;) in order to get the result.
Format:                        LN(expression)
Example:           SELECT LN(2.718281) FROM TABLEA;
Result:
                    
&#62; DB2, Oracle, and PostgreSQL support the LN [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/ln-natural-logarithm/</link>
			</item>
	<item>
		<title>FLOOR (round downward)</title>
		<description><![CDATA[he FLOOR function returns the largest integer less than the input value.
Format:                        FLOOR(expression)
Example:           SELECT FLOOR(10.8) FROM TABLEA;
Result:
                    
&#62;DB2, MySQL, Oracle, and SQL Server support FLOOR.
&#62; Access, PostgreSQL, and Sybase don&#8217;t support FLOOR.
 
]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/floor-round-downward/</link>
			</item>
	<item>
		<title>EXTRACT (part of date/time)</title>
		<description><![CDATA[The EXTRACT function extracts parts from a date and gives a datetime value.
Format: EXTRACT(date_part FROM expression)
Date_parts are:
CENTURY ................................. PostgreSQL .................
DAY ............. Access DB2 MySQL Oracle PostgreSQL SQL Server Sybase
DAY_HOUR ................... MySQL ...................................
DAY_MINUTE ................. MySQL ...................................
DAY_SECOND ................. MySQL ...................................
DAYOFYEAR .......................................... SQL Server ......
DECADE .................................. PostgreSQL .................
DOW (day of week) ....................... PostgreSQL .................
DOY (day of year) ....................... [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/extract-part-of-datetime/</link>
			</item>
	<item>
		<title>EXP (power of e)</title>
		<description><![CDATA[The EXP function returns the value of the natural logarithm to the base e. The mathematical constant e is approximately 2.718281828&#8230; raised to the power of the specified value.
Format:                        EXP(expression)
Example:           SELECT EXP(1) FROM TABLEA;
Result:
                    
&#62; Access, DB2, MySQL, PostgreSQL, SQL Server, and Sybase support the [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/exp-power-of-e/</link>
			</item>
	<item>
		<title>CHAR_LENGTH (no. of characters)</title>
		<description><![CDATA[CHARACTER_LENGTH gives the number of characters in a character string (not the number of bytes)
and returns an integer.
Format:                         CHARACTER_LENGTH(string)
Example:           SELECT CUST_NAME,
                         [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/char_length-no-of-characters/</link>
			</item>
	<item>
		<title>CEILING or CEIL (round upward)</title>
		<description><![CDATA[The CEILING function returns the smallest integer greater than the input value.
Format:            CEILING(expression)
Example:           SELECT CEILING(10.2) FROM TABLEA;
Result:
                     
&#62; DB2 supports CEILING and CEIL.
&#62; MySQL, and SQL Server support only CEILING.
&#62; Oracle supports only CEIL.
&#62; [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/ceiling-or-ceil-round-upward/</link>
			</item>
	<item>
		<title>BIT_LENGTH (no. of bits)</title>
		<description><![CDATA[The BIT_LENGTH function returns the number of bits contained in the value of the argument.
Format: BIT_LENGTH(expression)
Example: SELECT BIT_LENGTH('ABC') FROM TABLEA;
Result:
                    
&#62; PostgreSQL, and Sybase support BIT_LENGTH.
&#62; Access, DB2, MySQL, Oracle, and SQL Server don&#8217;t support BIT_LENGTH.
&#62; Oracle uses LENGTHB.
 
]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/bit_length-no-of-bits/</link>
			</item>
	<item>
		<title>ABSOLUTE or ABS (absolute value)</title>
		<description><![CDATA[The ABSOLUTE or ABS function converts the numeric value in the argument to its absolute value. In other words, a negative value is changed to a positive value.
Format:            ABS(expression)
Example:           SELECT ABS(-1) FROM TABLEA;
Result:
                    
&#62; [...]]]></description>
		<link>http://www.computerbasedtraininginc.com/blog/2008/12/absolute-or-abs-absolute-value/</link>
			</item>
</channel>
</rss>
