All Forums Analytics
nichunchun 6 posts Joined 01/09
10 Mar 2009
substring(accno,7,10)

Hi, I run query including "substring(accno,7,10)" in queryman ( query teradata directly ) and returned some result. I tried to use same query in SAS, show " Error: Teradata prepare: Syntax error: expected something between the word 'accno' and ','" . Somebody knows how to fix it? How to replace substring?

Fred 1096 posts Joined 08/04
10 Mar 2009

Either substr(accno,7,10) or substring(accno from 7 for 10) will work.The substring(accno,7,10) is ODBC function syntax - i.e. valid only if you are using ODBC driver and have SQL Extensions enabled.

Nusrat 4 posts Joined 10/11
14 Oct 2011

Ya you will have to write it as substring(accno from 7, 10)

Honeydews 1 post Joined 12/12
23 Dec 2012

Thanks..It helped me too

Sourabh_Rox 2 posts Joined 08/13
02 Sep 2013

i want to extract character from 'forecast_version_number' column.
this column contains value 'Sales Planning Forecast 3' I want to extract value '3' from it.
 how can i do it.
I have used following code:
select substr('Forecast_Version_Num',character_length('Forecast_Version_Num')-1,character_length('Forecast_Version_Num')) from edwwt.SIP_ESSBSE_FCT_STG;

KS42982 137 posts Joined 12/12
06 Sep 2013

I think your SQL would give you the integer extracted out of your forecast_version_number field.
If you want to extract characters from the forecast_version_number field, you could do SUBSTR from 1 to the second last character of the field - character_length('Forecast_Version_Num')-1 - that will extract characters out (provided integer value is always at the end).

You must sign in to leave a comment.