All Forums Database
Sunar 59 posts Joined 02/08
27 Jul 2009
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

pawan0608 101 posts Joined 12/07
27 Jul 2009

Hi Javeed,Use SQL like this for TAB keysel count(1) from db1.emp where emp_det ='%' || CHR(9) || '%' same way you can sepcify the ASCII codes for linefeed and carriage return~Pawan

Sunar 59 posts Joined 02/08
27 Jul 2009

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

pawan0608 101 posts Joined 12/07
27 Jul 2009

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 Assistantwhich version of TD are u using ?~Pawan

pawan0608 101 posts Joined 12/07
27 Jul 2009

Hey,ur SQL is wrongsel count(1) from db1.emp where emp_det ='%' || CHR(9) || '%' correct is sel count(1) from db1.emp where emp_det like '%' || CHR(9) || '%'

CarlosAL 512 posts Joined 04/08
27 Jul 2009

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.

pawan0608 101 posts Joined 12/07
27 Jul 2009

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:

pawan0608 101 posts Joined 12/07
27 Jul 2009

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:

CarlosAL 512 posts Joined 04/08
27 Jul 2009

>>Hey it is running fine at my endThis 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.

pawan0608 101 posts Joined 12/07
27 Jul 2009

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 = BTETlet me verify

CarlosAL 512 posts Joined 04/08
27 Jul 2009

>>"(CHR and ASCII) are standard TD functions."Could you point to the exact place in the Teradata Documentation where this is described?Cheers.Carlos.

Adeel Chaudhry 773 posts Joined 04/08
27 Jul 2009

Hello,CHR and ASCII are not Teradata built-in functions. If anyone is able to execute those functions, then off-course UDFs (most probably OracleUDFs) are installed on that Teradata system.And it has absolutely nothing to do with the settings!So, following are two possible solutions:- Install OracleUDFs from "http://www.teradata.com/DownloadCenter/Topic9228-137-1.aspx" and use CHR/ASCII function- Use "'09'xC" (perhaps a better solution)HTH!Regards,Adeel

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

CarlosAL 512 posts Joined 04/08
28 Jul 2009

>>"CHR and ASCII are not Teradata built-in functions. " Tell us something we don't know.>>"If anyone is able to execute those functions, then off-course UDFs (most probably OracleUDFs) are installed on that Teradata system."As I stated before.>>"- Use "'09'xC" (perhaps a better solution)"Well, not 'perhaps': It's POSITIVELY a better solution to use Teradata syntax because:·Teradata syntax is the syntax for Teradata systems, as Oracle syntax is the syntax for Oracle systems (Let's stop relying on Oracle background, it often DOES NOT FIT in Teradata.).·You cannot be sure if there are oracle UDF's compiled in a given system. (Go to one customer, try to execute SELECT CHR()... and, if error, go to the DBA to compile the Oracle UDF's for you because you don't know how to use '09'xC... He probably will show you the way out)Cheers.Carlos.

Sunar 59 posts Joined 02/08
28 Jul 2009

Hi Pawan and All,Thanks all for your responses. I tired the same way pawan, but its throughing error. BTEQ -- Enter your DBC/SQL request or BTEQ command: SELECT '-'||CHR(9)||'-';SELECT '-'||CHR(9)||'-';SELECT '-'||CHR(9)||'-'; $ *** Failure 3706 Syntax error: expected something between '(' and the integer '9'. Statement# 1, Info =19 *** Total elapsed time was 1 second.I think, UDF's are there in your system.But I got the solution by placing the hexa decimal value as :sel 'hi'||'09'XC||'bye'; *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second.(('hi'||' ')||'bye')--------------------hi byeThanks

Sunar 59 posts Joined 02/08
29 Jul 2009

Hi Pawan and All,Thanks all for your responses. I tired the same way pawan, but its throughing error. BTEQ -- Enter your DBC/SQL request or BTEQ command: SELECT '-'||CHR(9)||'-';SELECT '-'||CHR(9)||'-';SELECT '-'||CHR(9)||'-'; $ *** Failure 3706 Syntax error: expected something between '(' and the integer '9'. Statement# 1, Info =19 *** Total elapsed time was 1 second.I think, UDF's are there in your system.But I got the solution by placing the hexa decimal value as :sel 'hi'||'09'XC||'bye'; *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second.(('hi'||' ')||'bye')--------------------hi byeThanks

pawan0608 101 posts Joined 12/07
31 Jul 2009

Can anyone tell me the equivalent way in Teradata for ASCII function ?

dnoeth 4628 posts Joined 11/04
31 Jul 2009

There's a CHAR2HEXINT function which return a hex-string, e.g.select char2hexint('bla'); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 seconds.Char2HexInt('bla')------------------0062006C0061Literals are always UNICODE.Dieter

Dieter

CarlosAL 512 posts Joined 04/08
03 Aug 2009

>>Literals are always UNICODE.But you can use TRANSLATE to convert to ASCII (latin):select char2hexint('bla'), CHAR2HEXINT(TRANSLATE('bla' USING UNICODE_TO_LATIN)); *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second.Char2HexInt('bla') Char2HexInt(Translate('bla' using UNICODE_TO_LATIN))------------------ ----------------------------------------------------0062006C0061 626C61HTHCheers.Carlos.

ANIMESH.DUTTA 35 posts Joined 05/09
03 Aug 2009

The CHR(9) represents character.And CHR(9) is working fine in Teradata V2R6.I've checked through my Queryman (v 7.2.0.03) - it's perfectly giving character!!!

Animesh Dutta
Teradata Certified Master

CarlosAL 512 posts Joined 04/08
03 Aug 2009

>>"The CHR(9) represents character.>>And CHR(9) is working fine in Teradata V2R6.>>I've checked through my Queryman (v 7.2.0.03) - it's perfectly giving character!!!"Did you read anything of this thread?Cheers.Carlos.

You must sign in to leave a comment.