Finding only numneric values or only alphabetic values inside a alphanumeric column in Teradata.

Teradata: Numeric or Alphabetic data in Alphanumeric Column

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.

• Find Only Numeric Data
• Find Only Character Data
• Find Only Alphanumeric 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');

Approach 1: Simplest approach which may work in any of the database, here we have to specifically add "CASESPECIFIC" since Teradata is case insensitive.

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


Approach 2: In this approach, REGEXP_INSTR function is used to identify the position of any character in the String. And if any alphabetic character is not present then function will return 0.

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


Approach 3: In this approach, OTRANSLATE function is used to remove all the numeric value and if length of output string is Zero after removing all numeric values. That means it contains only numeric data.

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


Approach 4: In this approach, OTRANSLATE function is used to remove all the alphabetic values and if length of output string is equal to length of original string after removing all alphabetic value. That means it contains only numeric data.

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


Approach 5: In this approach, OTRANSLATE function is used to remove all the alphabetic values and if output string is equal to original string after removing all alphabetic value. That means it contains only numeric data.

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

Approach 1 :In this approach, REGEXP_INSTR function is used to identify the position of any numeric value in the String. And if any numeric character is not present then function will return 0.

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


Approach 2: In this approach, REGEXP_SUBSTR function is used to get first any numeric value in the String. And if output string is null that means data contains only Alphabetic data.

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


Approach 3: In this approach, OTRANSLATE function is used to remove all the numeric values and if length of output string is equal to length of original string after removing all numeric value. That means it contains only Alphabetic data.

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


Approach 4: In this approach, OTRANSLATE function is used to remove all the numeric values and if output string is equal to original string after removing all numeric value. That means it contains only Alphabetic data.

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


Approach 5: In this approach, OTRANSLATE function is used to remove all the Alphabetic values and if length of output string is Zero after removing all Alphabetic values. That means it contains only Alphabetic data.

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

Approach 1: This approach is combination of 2 OTRANSLATE functions to identify data with numeric values & to identify data with alphabetic values.

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


Approach 2: This approach is combination of 2 REGEXP_INSTR functions to identify data with numeric values & to identify data with alphabetic values.

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