All Forums Database
20 Aug 2008
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_FFor 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 wordThanks,Vijayalakshmi.

Grao 48 posts Joined 08/06
20 Aug 2008

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

-Grao

21 Aug 2008

Thanks Grao. Is there any possibility to achieve this using FORMAT option.

arunprakash24 3 posts Joined 07/08
25 Aug 2008

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 supportformat option.Try to execute the same query in BTEQ mode. You will get the expected answer.Regards,Arun Prakash.

Fred 1096 posts Joined 08/04
29 Aug 2008

SELECT SUBSTRING(LEAD_SRC FROM 1 FOR 1) FROM ITO_LEAD_F

Adeel Chaudhry 773 posts Joined 04/08
01 Sep 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.