How to get a column length defined in the table schema in teradata

Question

I need to find difference between length of a column value with column length defined while creating table.

Ex: I have a table schema as below.

CREATE TABLE `employee` (
  `id` int(10) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `subject` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

with values

id  name    subject 
1   Alex    maths   
2   Bob     maths   
3   Clark   science 
4   Dave    maths   

Now I need the output as

id  name    subject len(name) remain_space 
1   Alex    maths    4           6
2   Bob     maths    3           7
3   Clark   science  5           5
4   Dave    maths    4           6

Here the remain space is calculated using the formula (10 - len(name)) ,where 10 is the max.length of the column 'name' defined while creating table. How do I achieve this in Teradata?

Answer 1

The Standard SQL function to get the length of a string is named Char_Length:

10 - Char_Length(name)

If you don't want to hardcode the defined VarChar length:

RegExp_Substr(TYPE(name), '[0-9]+') - Char_Length(name)