Using String Functions in SQL Scripts
The following String Functions can be used in QODBC to obtain various results in your SQL statements:-
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 “A Plus B”, “Name” FROM Customer
Returns:
A Plus B 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 “ST Zip”, “Name” FROM Customer
Returns:
ST Zip 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 155 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 "Left 5", "Name" FROM Customer
Returns:
Left 5 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 "Location of A", "Name" FROM Customer
Returns:
Location of A 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 "Right 5", "Name" FROM Customer
Returns:
Right 5 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 "Ten Spaces", "Name" FROM Customer
Returns:
Ten Spaces 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 "Middle 5 Characters", "Name" FROM Customer
Returns:
Middle 5 Characters 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.
String Functions can also be nested
Example:
SELECT {fn LEFT({fn UCASE("Name")}, 5)} AS "LeftUCase", "Name" FROM Customer
Returns:
LeftUCase 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.
QODBC Convert
CONVERT(value_exp, data_type) - The function returns the value specified by value_exp converted to the specified data_type, where data_type is one of the following keywords:
SQL_BIGINT, SQL_BINARY, SQL_BIT, SQL_CHAR, SQL_DATE, SQL_DECIMAL, SQL_DOUBLE, SQL_FLOAT, SQL_INTEGER, SQL_LONGVARBINARY, SQL_LONGVARCHAR, SQL_NUMERIC, SQL_REAL, SQL_SMALLINT, SQL_TIME, SQL_TIMESTAMP, SQL_TINYINT, SQL_TYPE_DATE, SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, SQL_VARBINARY, SQL_VARCHAR
Example:
SELECT ('0'+ {fn CONVERT(Id, SQL_VARCHAR)}) AS "IDString" From Company
Returns:
For more functions click here.
|
