When we speak about a “String”, it does not necessarily have to be a character string. It also can be a byte or a numeric constant. While CHAR string includes VARCHAR and CLOB, BYTE includes VARBYTE and BLOB and numeric includes DECIMAL, FLOAT, INTEGER, SMALLINT etc. Following is a small list of string manipulators present in Teradata;

1. Concatenation Operator (‘||’) => To concatenate two or more strings, a string can be a byte, numeric or a character string or string expression(s).Example

SELECT ‘FirstName’ || ‘ ‘ || ‘LastName’ as Full_Name;

Would result in

Full_Name
FirstName LastName

Result type of the concatenation depends on the types of argument in the expression. If the arguments are CHAR then the result type would also be CHAR. In the case of NUMERIC, the result type would still be a CHAR and in the case of BYTE; the result type is BYTE.

2.CHAR2HEXINT=> if you want to know the hexadecimal value of a particular character then this is the function to use.

SELECT CHAR2HEXINT (‘A’) would result in the value ‘0041’.

3.INDEX => This one needs two arguments and returns the position in 1^st argument where the 2^nd argument (string expression) starts. This is not ANSI compatible; hence it is better to use the POSITION function in such case.

SELECT index (‘Name’, ‘a’) OR SELECT position (‘a’ in ‘Name’);

Would result in the same output as 2.

4.LOWER => Used to display character string all in lower case. In other words it returns a string that is identical to argument passed but in lower case.

SELECT (‘NAME IN UPPER’) would return as ‘name in upper’.

5.POSITION => Returns the position of 2^nd argument where 1^st argument starts.

SELECT position (‘a’ in ‘Name’); would return 2.

6.SOUNDEX => Returns a character string that represents the Soundex code for an argument passed. This Soundex is a system that codes surnames having the same or similar sounds, but different spellings. Example

SELECT soundex (‘Tom’);
SELECT soundex (‘Toam’);

would result in the same value as T500.

7.SUBSTRING/SUBSTR => Extracts a substring from a named string based on position. Here is the syntax

SELECT SUBSTR (<column name> FROM <starting position> FOR <ending position>)

8.TRIM => As the name suggests, this function is used to remove extra spaces in any character or byte string expression. Here is the syntax;

TRIM (BOTH | LEADING |TRAILING { trim_character } FROM {string_expression})

9.UPPER => Used to display character string all in UPPER case. In other words it returns a string that is identical to argument passed but in UPPER case.

SELECT (‘lower’) would return as ‘LOWER’.

Our Random Articles

• Somdev Devvarman ousts Xavier Malisse 6-1, 3-6,7-6(5)
• How To Choose A Good Online Traffic School
• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns

More Links