How to fetch only the first character from a word using FORMAT option

Database

How to fetch only the first character from a word using FORMAT option

Consider the below sql statement

SELECT (LEAD_SRC (FORMAT 'X')) FROM ITO_LEAD_F

For the LEAD_SRC column, even after using the FORMAT option results in actual value. FORMAT has no effect on the above statment.

Please help me out to get only first character from a word

Thanks,
Vijayalakshmi.
5 REPLIES
Enthusiast

Re: How to fetch only the first character from a word using FORMAT option

sel cast(col_name as char(1)) from tbl_nm;

Re: How to fetch only the first character from a word using FORMAT option



Thanks Grao.

Is there any possibility to achieve this using FORMAT option.

Re: How to fetch only the first character from a word using FORMAT option

Hi ,

The query which you have given will return the first character.

You will be getting all the text in column if the too which you are using to run queries doesn't support

format option.

Try to execute the same query in BTEQ mode. You will get the expected answer.

Regards,
Arun Prakash.
Teradata Employee

Re: How to fetch only the first character from a word using FORMAT option


SELECT SUBSTRING(LEAD_SRC FROM 1 FOR 1) FROM ITO_LEAD_F
Teradata Employee

Re: How to fetch only the first character from a word using FORMAT option

Hello,

The following query is a valid query for this task in BTEQ:

SELECT 'abcd' (FORMAT 'X');
Result: 'a'

Running the same query in SQL Assistant will return the same whole string i.e. 'abcd'.

This is because SQL Assistant is connected to Teradata via ODBC and BTEQ uses CLI. And ODBC doesnot support FORMAT clause. This is why the results are different in SQL Assistant and BTEQ.

Regards,

Adeel