Search              building menu display...

QODBC Functions Reference

This is a list of all of the SQL functions supported by the QODBC Driver and their associated syntax..

QODBC String Functions

ASCII(string_exp) - Returns the ASCII code value of the leftmost character of string_exp as integer.

Example:

SELECT {fn ASCII("Name")} AS "ASCII", "Name" FROM Customer

Returns:

ASCII	Name
65	Abercrombie, Kristy
66	Baker, Chris
66	Balak, Mike
66	Barley, Renee
66	Bolinski, Rafal
50	2nd story addition
66	Bristol, Sonya
66	Burch, Jason
66	Burney, Tony
50	2nd story addition
50	2nd story addition
49	155 Wilks Blvd.
55	75 Sunset Rd.

 

CHAR(code) - Returns the character that has the ASCII code value specified by code. The value of code should be between 0 and 255; otherwise, the return value is data source-dependent.

Example:

SELECT {fn CHAR(65)} +  {fn CHAR(66)} AS "APlusB", "Name" FROM Customer

Returns:

APlusB	Name
AB	Abercrombie, Kristy
AB	2nd story addition
AB	2nd story addition
AB	2nd story addition
AB	155 Wilks Blvd.
AB	75 Sunset Rd.

 

CONCAT(string_exp1, string_exp2) - Returns a character string that is the result of concatenating string_exp2 to string_exp1. If the column represented by string_exp1 or string_exp2 contains a NULL value, a NULL value will be returned.

Example:

SELECT {fn CONCAT("BillAddressState", "BillAddressPostalCode")} AS "STZip", "Name" FROM Customer

Returns:

STZip	Name
CA94326	Abercrombie, Kristy
CA94327	2nd story addition
CA94482	2nd story addition
CA94326	2nd story addition
CA94482	155 Wilks Blvd.
CA94482	75 Sunset Rd.

                  

DIFFERENCE(string_exp1, string_exp2) - Returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.

Example:

SELECT {fn DIFFERENCE("Name", 'Abercrombie, Kristy')} AS "Difference", "Name" FROM Customer

Returns:

Difference	Name
0	Abercrombie, Kristy
1102829	2nd story addition
1102829	2nd story addition
1102829	2nd story addition
1001829	55 Wilks Blvd.
99949	75 Sunset Rd.

 

INSERT(string_exp1, start, length, string_exp2) - Returns a character string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp1, beginning at start.

Example:

SELECT {fn INSERT("Name", 3, 2, '*Inserted*')} AS "Inserted", "Name" FROM Customer

Returns:

Inserted	Name
Ab*Inserted*crombie, Kristy	Abercrombie, Kristy
2n*Inserted*story addition	2nd story addition
2n*Inserted*story addition	2nd story addition
2n*Inserted*story addition	2nd story addition
15*Inserted*Wilks Blvd.	155 Wilks Blvd.
75*Inserted*unset Rd.	75 Sunset Rd.

 

LCASE(string_exp) - Converts all upper case characters in string_exp to lower case.

Example:

SELECT {fn LCASE("Name")} AS "LCase", "Name" FROM Customer

Returns:

LCase	Name
abercrombie, kristy	Abercrombie, Kristy
2nd story addition	2nd story addition
2nd story addition	2nd story addition
2nd story addition	2nd story addition
155 wilks blvd.	155 Wilks Blvd.
75 sunset rd.	75 Sunset Rd.

 

LEFT(string_exp, count) - Returns the leftmost count of characters of string_exp.

Example:

SELECT {fn LEFT("Name", 5)} AS "Left5", "Name" FROM Customer

Returns:

Left5	Name
Aberc	Abercrombie, Kristy
2nd s	2nd story addition
2nd s	2nd story addition
2nd s	2nd story addition
155 W	155 Wilks Blvd.
75 Su	75 Sunset Rd.

 

LENGTH(string_exp) - Returns the number of characters in string_exp, excluding trailing blanks and the string termination character.

Example:

SELECT {fn LENGTH("Name")} AS "Length", "Name" FROM Customer

Returns:

Length	Name
19	Abercrombie, Kristy
18	2nd story addition
18	2nd story addition
18	2nd story addition
15	155 Wilks Blvd.
13	75 Sunset Rd.

 

LOCATE(string_exp1, string_exp2[, start]) - Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first position in string_exp2 unless the optional argument, start is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned.

Example:

SELECT {fn LOCATE('a', "Name", 2)} AS "LocationOfA", "Name" FROM Customer

Returns:

LocationOfA	Name
0	Abercrombie, Kristy
11	2nd story addition
11	2nd story addition
11	2nd story addition
0	155 Wilks Blvd.
0	75 Sunset Rd.

 

LTRIM(string_exp) - Returns the characters of string_exp, with leading blanks removed.

Example:

SELECT {fn LTRIM("Name")} AS "LTrim", "Name" FROM Customer

Returns:

LTrim	Name
Abercrombie, Kristy	Abercrombie, Kristy
2nd story addition	2nd story addition
2nd story addition	2nd story addition
2nd story addition	2nd story addition
155 Wilks Blvd.	155 Wilks Blvd.
75 Sunset Rd.	75 Sunset Rd.

 

REPEAT(string_exp, count) -

Example:

SELECT {fn REPEAT(‘XO’, 5)} AS "Repeat", "Name" FROM Customer

Returns:

Repeat	Name
XOXOXOXOXO	Abercrombie, Kristy
XOXOXOXOXO	2nd story addition
XOXOXOXOXO	2nd story addition
XOXOXOXOXO	2nd story addition
XOXOXOXOXO	155 Wilks Blvd.
XOXOXOXOXO	75 Sunset Rd.

 

 

RIGHT(string_exp, count) - Returns the rightmost count of characters of string_exp.

Example:

SELECT {fn RIGHT(“Name”, 5)} AS "Right5", "Name" FROM Customer

Returns:

Right5	Name
risty	Abercrombie, Kristy
ition	2nd story addition
ition	2nd story addition
ition	2nd story addition
Blvd.	155 Wilks Blvd.
t Rd.	75 Sunset Rd.

 

RTRIM(string_exp) - Returns the characters of string_exp, with trailinging blanks removed.

Example:

SELECT {fn RTRIM("Name")} AS "RTrim", "Name" FROM Customer

Returns:

RTrim	Name
Abercrombie, Kristy	Abercrombie, Kristy
2nd story addition	2nd story addition
2nd story addition	2nd story addition
2nd story addition	2nd story addition
155 Wilks Blvd.	155 Wilks Blvd.
75 Sunset Rd.	75 Sunset Rd.

 

SOUNDEX(string_exp) - Returns a character string representing the sound of the words in string_exp.

Example:

SELECT {fn SOUNDEX("Name")} AS "Soundex", "Name" FROM Customer

Returns:

Soundex	Name
ABARCRAMBACRACDA	Abercrombie, Kristy
AMDACDARADADAM	2nd story addition
AMDACDARADADAM	2nd story addition
AMDACDARADADAM	2nd story addition
ALCABLBDA	155 Wilks Blvd.
ACAMCADARDA	75 Sunset Rd.

 

SPACE(count) - Returns a character string consisting of count spaces.

Example:

SELECT ‘[‘ + {fn SPACE(10)} + ‘]’ AS "TenSpaces", "Name" FROM Customer

Returns:

TenSpaces	Name
[          ]	Abercrombie, Kristy
[          ]	2nd story addition
[          ]	2nd story addition
[          ]	2nd story addition
[          ]	155 Wilks Blvd.
[          ]	75 Sunset Rd.

 

SUBSTRING(string_exp, start, length) - Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters.

Example:

SELECT {fn SUBSTRING("Name", 2, 5)} AS "Middle5Characters", "Name" FROM Customer

Returns:

Middle5Characters	Name
bercr	Abercrombie, Kristy
nd st	2nd story addition
nd st	2nd story addition
nd st	2nd story addition
55 Wi	155 Wilks Blvd.
5 Sun	75 Sunset Rd.

 

UCASE(string_exp) - Converts all lower case characters in string_exp to upper case.

Example:

SELECT {fn UCASE("Name")} AS "UCase", "Name" FROM Customer

Returns:

UCase	Name
ABERCROMBIE, KRISTY	Abercrombie, Kristy
2ND STORY ADDITION	2nd story addition
2ND STORY ADDITION	2nd story addition
2ND STORY ADDITION	2nd story addition
155 WILKS BLVD.	155 Wilks Blvd.
75 SUNSET RD.	75 Sunset Rd.

 

QODBC Numeric Functions

 

ABS(numeric_exp) – Returns the absolute value of numeric_exp.

Example:

SELECT "Name", {fn ABS(Balance)} AS "ABSBalance", "Balance" FROM Customer

Returns:

Name	ABSBalance	Balance