concatenate

This is Ultimate Teradata String Function Cheat Sheet:
We will start by creating a table and will insert few records in it. This table data will be used in subsequent examples:

CREATE MULTISET VOLATILE TABLE  vt_president
(
president_id VARCHAR(15) NOT NULL,
president_name VARCHAR(100)
)
UNIQUE PRIMARY INDEX (president_id)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt_president SEL ‘001’,’George Washington’;
INSERT INTO vt_president SEL ‘002’,’John Adams’;
INSERT INTO vt_president SEL ‘003’,’Thomas Jefferson’;
INSERT INTO vt_president SEL ‘004’,’James Madison’;
INSERT INTO vt_president SEL ‘005’,’James Monroe’;
INSERT INTO vt_president SEL ‘006’,’John Quincy Adams’;
INSERT INTO vt_president SEL ‘007’,’Andrew Jackson’;
INSERT INTO vt_president SEL ‘008’,’Martin Van Buren’;
INSERT INTO vt_president SEL ‘009’,’William Henry Harrison’;
INSERT INTO vt_president SEL ‘010’,’John Tyler’;
INSERT INTO vt_president SEL ‘011’,’James K.Polk’;
INSERT INTO vt_president SEL ‘012’,’Zachary Taylor’;
INSERT INTO vt_president SEL ‘013’,’Millard Fillmore’;
INSERT INTO vt_president SEL ‘014’,’Franklin Pierce’;
INSERT INTO vt_president SEL ‘015’,’James Buchanan’;
INSERT INTO vt_president SEL ‘016’,’Abraham Lincoln’;
INSERT INTO vt_president SEL ‘017’,’Andrew Johnson’;
INSERT INTO vt_president SEL ‘018’,’Ulysses S.Grant’;
INSERT INTO vt_president SEL ‘019’,’Rutherford B.Hayes’;
INSERT INTO vt_president SEL ‘020’,’James Garfield’;
INSERT INTO vt_president SEL ‘021’,’Chester A.Arthur’;
INSERT INTO vt_president SEL ‘022’,’Grover Cleveland’;
INSERT INTO vt_president SEL ‘023’,’Benjamin Harrison’;
INSERT INTO vt_president SEL ‘024’,’Grover Cleveland’;
INSERT INTO vt_president SEL ‘025’,’William McKinley’;
INSERT INTO vt_president SEL ‘026’,’Theodore Roosevelt’;
INSERT INTO vt_president SEL ‘027’,’William Howard Taft’;
INSERT INTO vt_president SEL ‘028’,’Woodrow Wilson’;
INSERT INTO vt_president SEL ‘029’,’Warren G.Harding’;
INSERT INTO vt_president SEL ‘030’,’Calvin Coolidge’;
INSERT INTO vt_president SEL ‘031’,’Herbert Hoover’;
INSERT INTO vt_president SEL ‘032’,’Franklin D.Roosevelt’;
INSERT INTO vt_president SEL ‘033’,’Harry S.Truman’;
INSERT INTO vt_president SEL ‘034’,’Dwight D.Eisenhower’;
INSERT INTO vt_president SEL ‘035’,’John F.Kennedy’;
INSERT INTO vt_president SEL ‘036’,’Lyndon B.Johnson’;
INSERT INTO vt_president SEL ‘037’,’Richard M.Nixon’;
INSERT INTO vt_president SEL ‘038’,’Gerald R.Ford’;
INSERT INTO vt_president SEL ‘039’,’James Carter’;
INSERT INTO vt_president SEL ‘040’,’Ronald Reagan’;
INSERT INTO vt_president SEL ‘041’,’George H.W.Bush’;
INSERT INTO vt_president SEL ‘042’,’William J.Clinton’;
INSERT INTO vt_president SEL ‘043’,’George W.Bush’;
INSERT INTO vt_president SEL ‘044’,’Barack Obama’;

SEL * FROM vt_president;

Teradata SELECT
Teradata SELECT

/*** ========= FUNCTIONS  ========= ***/

/**** concatenate ****/
SEL president_id,president_name,president_id||’-‘||president_name AS str_concat FROM vt_president;
 
Teradata String Concatenation
Teradata String Concatenation

/**** char2hex ****/
SEL president_id,president_name, CHAR2HEXINT(president_name) AS str_char2hex FROM vt_president;

Teradata String Char2Hex
Teradata String Char2Hex

/**** index ****/
SEL president_id,president_name, INDEX(president_name,’.’) AS str_index FROM vt_president;

/**** position ****/
SEL president_id,president_name, POSITION(‘.’ IN president_name) AS str_position FROM vt_president;

Teradata String Position or INDEX
Teradata String Position or INDEX

/**** lower ****/
SEL president_id,president_name, LOWER(president_name) AS str_lower FROM vt_president;

Teradata String Lower
Teradata String Lower

/**** SUBSTRING ****/
SEL president_id,president_name, SUBSTRING(president_name from 1 for 5) AS str_substring FROM vt_president;

/**** SUBSTR ****/
SEL president_id,president_name, SUBSTR(president_name,1,5) AS str_substr FROM vt_president;

Teradata String Substring
Teradata String Substring

 

/**** TRANSLATE ****/
SEL president_id,president_name, TRANSLATE(president_name USING UNICODE_TO_LATIN) AS str_translate FROM vt_president;

Teradata String Translate
Teradata String Translate

/**** TRANSLATE_CHK ****/
SEL president_id,president_name, TRANSLATE_CHK(president_name USING UNICODE_TO_LATIN) AS str_translate_chk FROM vt_president;

Teradata String Translate_check
Teradata String Translate_check

/**** TRIM ****/
SEL president_id,president_name, TRIM(president_name) AS str_trim FROM vt_president;

Teradata String Trim
Teradata String Trim

 

/**** UPPER ****/
SEL president_id,president_name, UPPER(president_name) AS str_upper FROM vt_president;

Teradata String UPPER
Teradata String UPPER

/*** ========= OPERATORS  ========= ***/

/**** EQUAL ****/
SEL * FROM vt_president WHERE president_id=’001′;
SEL * FROM vt_president WHERE president_id EQ ‘001’;

Teradata String Equal
Teradata String Equal

/**** NOT EQUAL ****/
SEL * FROM vt_president WHERE president_id<>’001′;
SEL * FROM vt_president WHERE president_id NE ‘001’;
SEL * FROM vt_president WHERE president_id ^= ‘001’;
SEL * FROM vt_president WHERE president_id NOT= ‘001’;

Teradata String Not Equal
Teradata String Not Equal

/**** LESS THAN ****/
SEL * FROM vt_president WHERE president_id<‘003’;
SEL * FROM vt_president WHERE president_id LT ‘003’;

Teradata String Less Than
Teradata String Less Than

/**** LESS THAN or EQUAL****/
SEL * FROM vt_president WHERE president_id<=’003′;
SEL * FROM vt_president WHERE president_id LE ‘003’;

Teradata String Less Than or Equal
Teradata String Less Than or Equal

 

/**** GREATER THAN ****/
SEL * FROM vt_president WHERE president_id>’033′;
SEL * FROM vt_president WHERE president_id GT ‘033’;

Teradata String GREATER Than
Teradata String GREATER Than

/**** GREATER THAN or EQUAL****/
SEL * FROM vt_president WHERE president_id>=’033′;
SEL * FROM vt_president WHERE president_id GE ‘033’;

Teradata String GREATER Than or Equal
Teradata String GREATER Than or Equal

/*** ========= MISC  ========= ***/

/**** CHARACTERS ****/
SEL president_id,president_name, CHARACTERS(president_name) AS str_CHARACTERS FROM vt_president;

/**** CHARS ****/
SEL president_id,president_name, CHARS(president_name) AS str_CHARS FROM vt_president;

/**** CHAR ****/
SEL president_id,president_name, CHAR(president_name) AS str_CHAR FROM vt_president;

/**** CHARACTER_LENGTH ****/
SEL president_id,president_name, CHARACTER_LENGTH(president_name) AS str_CHARACTER_LENGTH FROM vt_president;

Teradata String CHARACTERS
Teradata String CHARACTERS

/**** BETWEEN ****/
SEL president_id,president_name FROM vt_president where president_id between ‘002’ and ‘006’;

Teradata String Between
Teradata String Between

/****NOT BETWEEN ****/
SEL president_id,president_name FROM vt_president WHERE president_id NOT BETWEEN ‘002’ AND ‘006’;

Teradata String NOT Between
Teradata String NOT Between

/**** LIKE ****/
SEL president_id,president_name FROM vt_president WHERE president_id like ‘00%’;

Teradata String LIKE
Teradata String LIKE

 

/**** NOT LIKE ****/
SEL president_id,president_name FROM vt_president WHERE president_id NOT LIKE ‘00%’;

Teradata String NOT LIKE
Teradata String NOT LIKE

 

Hope this helps !!!

One Reply to “String Functions”

Leave a Reply

Your email address will not be published. Required fields are marked *