# 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.

Creating Sample Table
```CREATE MULTISET TABLE alphanumeric_test(
id INTEGER,
user_id VARCHAR(50),
Name VARCHAR(100)
)
PRIMARY INDEX(id);
```

Insert Alphanumeric data in Table
```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

Finding Numeric Data in Alphanumeric Column

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
```

Finding Alphabetic Data in Alphanumeric Column

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
```

Finding only Alphanumeric data

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
```