Tab in column

Database
Enthusiast

Tab in column

Hi All,

Could anyone know how to find the tab, linefeed and carriage return present in a column.
Suppose I have a column emp_det, I want to find the number of rows which are having tabs, linefeed and carriage return.
I tried as:
sel count(1) from db1.emp where emp_det ='% %' ;--In between 2 %, I have kept one tab. The output showing is wrong. This is for tab. Don't know about linefeed and carriage return.
Do anyone have any suggestion ?

Thanks
19 REPLIES
Enthusiast

Re: Tab in column

Hi Javeed,

Use SQL like this for TAB key

sel count(1) from db1.emp where emp_det ='%' || CHR(9) || '%'

same way you can sepcify the ASCII codes for linefeed and carriage return

~Pawan

Enthusiast

Re: Tab in column

Hi Pawan,

Thanks for your reply.
I tried but, i think chr function is not available in TERADATA.

sel count(1) from db1.emp where emp_det ='%' || CHR(9) || '%'
$
*** Failure 3706 Syntax error: expected something between '(' and the integer '9'.
Statement# 1, Info =55
*** Total elapsed time was 1 second.

Thanks
Enthusiast

Re: Tab in column

It is strange, below SQL is executing fine at my end and giving desired results.

SEL * FROM tmp.pawan where col2 like '%' || CHR(9) || '%'

I m running it through SQL Assistant

which version of TD are u using ?

~Pawan
Enthusiast

Re: Tab in column

Hey,

ur SQL is wrong

sel count(1) from db1.emp where emp_det ='%' || CHR(9) || '%'

correct is

sel count(1) from db1.emp where emp_det like '%' || CHR(9) || '%'
Junior Supporter

Re: Tab in column



And your SQL is wrong too. CHR() is NOT Teradata syntax:

SELECT '-'||CHR(9)||'-';
$
*** Failure 3706 Syntax error: expected something between '(' and the integer '9'.
Statement# 1, Info =19
*** Total elapsed time was 1 second.

The correct syntax is:

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT '-'||'09'xC||'-';

*** Query completed. One row found. One column returned
*** Total elapsed time was 1 second.

(('-'||' ')||'-')
-----------------
- -

Cheers.

Carlos.
Enthusiast

Re: Tab in column

I don't what version TD you are using, I have just run this query through BTEQ as well. Let me know if same query is not working at your end

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT 'a' || CHR(9) || 'b';

SELECT 'a' || CHR(9) || 'b';

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

(('a'||CHR(9))||'b')
--------------------
a b

BTEQ -- Enter your DBC/SQL request or BTEQ command:
Enthusiast

Re: Tab in column

Hey it is running fine at my end

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT '-'||CHR(9)||'-';

SELECT '-'||CHR(9)||'-';

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

(('-'||CHR(9))||'-')
--------------------
- -

BTEQ -- Enter your DBC/SQL request or BTEQ command:

Junior Supporter

Re: Tab in column



>>Hey it is running fine at my end

This is probably because you have the oracle functions compiled as UDF's in your DB, but I repeat: CHR() is NOT Teradata syntax.

The proper way in Teradata is as explained with '09'xC format.

Cheers.

Carlos.
Enthusiast

Re: Tab in column

I guess, they should be (CHR and ASCII) are standard TD functions.

I am not sure if it is something related to session setting. mine is

SESSION TRANSACTION = BTET

let me verify