There are many cases where alphanumeric data is stored in columns of Teradata tables. Sometime it is required to get only numeric data from alphanumeric column of a table. Sometime it is required to find only alphabetical data from alphanumeric column of a table. In this tutorial, we will see multiple approaches to achieve the required output. Illustration will be done by creating table with sample data.
CREATE MULTISET TABLE alphanumeric_test(
id INTEGER,
user_id VARCHAR(50),
Name VARCHAR(100)
)
PRIMARY INDEX(id);
INS alphanumeric_test(1,'alpha','Teradata');
INS alphanumeric_test(2,'beta','Oracle');
INS alphanumeric_test(3,'gamma','Vertica');
INS alphanumeric_test(4,'12345','Mysql');
INS alphanumeric_test(5,'123gamma','DB2');
INS alphanumeric_test(6,'54321','Python');
INS alphanumeric_test(7,'433alpha','Scala');
INS alphanumeric_test(8,'alpha4532','R');
INS alphanumeric_test(9,'24343','Hive');
INS alphanumeric_test(10,'PETA','Pig');
INS alphanumeric_test(11,'beta2322','Hbase');
INS alphanumeric_test(12,'324BETA','Hbase');
INS alphanumeric_test(12,'324GAMMA6545','Hbase');
Note: It is assumed that data contains only numbers & characters
SELECT id, user_id FROM alphanumeric_test
WHERE UPPER(user_id)=LOWER(user_id)(CASESPECIFIC);
Output:
Id User_Id
--- --------
6 54321
9 24343
4 12345
SELECT id, user_id FROM alphanumeric_test
WHERE REGEXP_INSTR(user_id,'[a-zA-Z]+')=0;
Output:
Id User_Id
--- --------
6 54321
9 24343
4 12345
SELECT id, user_id FROM alphanumeric_test
WHERE CHARACTER_LENGTH(OTRANSLATE(user_id,'0123456789',''))=0;
Output:
Id User_Id
--- --------
6 54321
9 24343
4 12345
SELECT id, user_id FROM alphanumeric_test
WHERE CHARACTER_LENGTH(OTRANSLATE(LOWER(user_id),'abcdefghijklmnopqrstuvwxyz',''))=CHARACTER_LENGTH(user_id);
Output:
Id User_Id
--- --------
6 54321
9 24343
4 12345
SELECT id, user_id FROM alphanumeric_test
WHERE OTRANSLATE(LOWER(user_id),'abcdefghijklmnopqrstuvwxyz','') = user_id;
Output:
Id User_Id
--- --------
6 54321
9 24343
4 12345
SELECT id, user_id FROM alphanumeric_test
WHERE REGEXP_INSTR(user_id,'[0-9]')=0;
Output:
Id User_Id
--- --------
3 gamma
10 PETA
1 alpha
2 beta
SELECT id, user_id,REGEXP_SUBSTR(user_id,'[0-9]') FROM alphanumeric_test
WHERE REGEXP_SUBSTR(user_id,'[0-9]') IS NULL;
Output:
Id User_Id
--- --------
3 gamma
10 PETA
1 alpha
2 beta
SELECT id, user_id FROM alphanumeric_test
WHERE CHARACTER_LENGTH(OTRANSLATE(user_id,'0123456789','')) = CHARACTER_LENGTH(user_id);
Output:
Id User_Id
--- --------
3 gamma
10 PETA
1 alpha
2 beta
SELECT id, user_id FROM alphanumeric_test
WHERE OTRANSLATE(user_id,'0123456789','') = user_id;
Output:
Id User_Id
--- --------
3 gamma
10 PETA
1 alpha
2 beta
SELECT id, user_id FROM alphanumeric_test
WHERE CHARACTER_LENGTH(OTRANSLATE(LOWER(user_id),'abcdefghijklmnopqrstuvwxyz',''))=0;
Output:
Id User_Id
--- --------
3 gamma
10 PETA
1 alpha
2 beta
SELECT id, user_id FROM alphanumeric_test
WHERE CHARACTER_LENGTH(OTRANSLATE(user_id,'0123456789',''))>0
AND CHARACTER_LENGTH(OTRANSLATE(LOWER(user_id),'abcdefghijklmnopqrstuvwxyz',''))>0;
Output:
Id User_Id
--- ---------
7 433alpha
11 beta2322
5 123gamma
12 324BETA
8 alpha4532
SELECT id, user_id FROM alphanumeric_test
WHERE REGEXP_INSTR(user_id,'[0-9]')>0
AND REGEXP_INSTR(user_id,'[a-zA-Z]')>0;
Output:
Id User_Id
--- ---------
7 433alpha
11 beta2322
5 123gamma
12 324BETA
8 alpha4532