substring() on huge text column

Database
Enthusiast

substring() on huge text column

Hi

I tried searching the forums, but I cannot find the "search" option on the site.

My problem:

I am using Teradata v13 and I need to do a substring on a huge text column.  The table DBC.DBQLSqlTbl has a column called "SqlTextInfo" (datatype:  varchar(31000)) - this is quite huge.

I need to substing some values in the header below.  My substring is working fine when the column "SqlTextInfo" is small. However, when the contents of "SqlTextInfo" is to long, the script gives an error.  (Some entries are over 10,000 characters long.)

My subsbring looks like this one (I have a few of them in my statement):

cast(substring (a.SqlTextInfo from (index(a.SqlTextInfo, 'User=') + 5)  for (index(a.SqlTextInfo, 'reportPath=') - (index(a.SqlTextInfo, 'User=') + 5))) as char(50)) as userName

The header that I need to inspect looks like this (everything between the /*...*/): /* Cognos User=BobJones reportPath=/content/folder[@name='Blah_Blah']/folder[@name='Blah']/report[@name='TheReport'] queryName=qMain REMOTE_ADDR=000.000.000.000 SERVER_NAME=the_server requestID=qsCyCw8GsMlwGjMqwq8Gq2CMldqjsl9v84yCwdqd */ Select abc, def, ghi from tableA where x=123

Like I said above, my script runs fine if the "SQLTextInfo" field is short, but if it is long, it fails with this error:  "SELECT Failed.  [2662] SUBSTR:  string subscript out of bounds."  

I've searched Google, but could not find a suitable solution. :(

Any ideas?

Thank you,

Herman

9 REPLIES
Enthusiast

Re: substring() on huge text column

It would be easier to export the required columns from dbc.dbqlsqltbl into a file; and then use grep /awk in Unix to extract in the required format

Enthusiast

Re: substring() on huge text column

Thank you for getting back to me Sachin.

Exporting to Unix would be easier, but unfortunately I need to run my script through Cognos Report Studio via tabular SQL.

My reporting requirement is to match reports ran in Cognos against the related Teradata SQL script for those Cognos reports.

Is there perhaps another way to split the text field before doing the substring?

H

Senior Apprentice

Re: substring() on huge text column

Hi Herman,

this error is not because of the long string, there are some rows where there's a "user=", but no "reportPath=" resulting in a negative value in FOR which errors out.

You need a WHERE condition or a CASE to filter:

CASE WHEN SqlTextInfo like ''%user=%reportPath=%' THEN SUBSTRING... END

Dieter

Senior Apprentice

Re: substring() on huge text column

Hi Herman,

this error is not because of the long string, there are some rows where there's a "user=", but no "reportPath=" resulting in a negative value in FOR which errors out.

You need a WHERE condition or a CASE to filter:

CASE WHEN SqlTextInfo like ''%user=%reportPath=%' THEN SUBSTRING... END

Dieter

Enthusiast

Re: substring() on huge text column

Hi Dieter

Thanks.  Your solution worked perfectly. 

I thought Teradata would create a null value and continue if the "reportPath=" was missing.

Thanks,

Herman

Re: substring() on huge text column

Hi Dieter, I am also having same issue but while running same query in SQL Assistant it's working fine but with BTEQ its giving error, also checked for non of records it's giving negative values. Also tried to explicitly define session as ANSI and all likewise, just trying to rectify why its not working in BTEQ but fine with SQL Assitant. Appreciate your help on this.
Senior Apprentice

Re: substring() on huge text column

Can you show the exact SQL which causes the error and details about the column types?

Re: substring() on huge text column

Hi,

 

Please see sql as below. Which run well in SQL assitant but not in BTEQ via Linux.

 

SELECT
ABC.field1
,CASE WHEN POSITION('Reason for Removal' IN ABC.field1) > 0 THEN
SUBSTR(ABC.field1
,POSITION('Reason for Removal' IN ABC.field1) + 29
,POSITION('»' IN SUBSTR(ABC.field1 ,POSITION('Reason for Removal' IN ABC.field1) + 29,LENGTH(ABC.field1)))-1)
END REASON_CODE1
/* Just Tried to check positing 

,POSITION('Reason for Removal' IN ABC.field1) + 29
,POSITION('»' IN SUBSTR(ABC.field1 ,POSITION('Reason for Removal' IN ABC.field1) + 29,LENGTH(ABC.field1)))-1 AS ABC*/
,CASE WHEN POSITION('Reason for Removal' IN ABC.field1) > 0 THEN
SUBSTRING(ABC.field1 FROM POSITION('Reason for Removal' IN ABC.field1) + 29 FOR
POSITION('»' IN SUBSTRING(ABC.field1 FROM POSITION('Reason for Removal' IN ABC.field1) + 29))- 1)
END AS Reason_Code
FROM ABC
----WHERE ABC LIKE ('%Reason for Removal%') ---commented as its already considered in case when condition

 

 Sample rows for reference. which is xml data.

1) <?xml version = '1.0'?><ihgfedcba><abcdefghi id="idvMethod"><!±CDATA±N/A»»></abcdefghi><abcdefghi id="status"><!±CDATA±Success»»></abcdefghi><abcdefghi id="type"><!±CDATA±Update»»></abcdefghi><abcdefghi id="Outbound Indicator"><!±CDATA±FALSE»»></abcdefghi><abcdefghi id="bcdfe as"><!±CDATA±10000180081900828210161020115510»»></abcdefghi><abcdefghi id="ACD VFCGD"><!±CDATA±ACD VFCGD cdvs»»></abcdefghi><abcdefghi id="Reason for Removal"><!±CDATA±Want to Test It Out»»></abcdefghi></ihgfedcba>
2) <?xml version = '1.0'?><ihgfedcba><abcdefghi id="idvMethod"><!±CDATA±N/A»»></abcdefghi><abcdefghi id="status"><!±CDATA±Success»»></abcdefghi><abcdefghi id="type"><!±CDATA±Update»»></abcdefghi><abcdefghi id="Outbound Indicator"><!±CDATA±FALSE»»></abcdefghi><abcdefghi id="bcdfe as"><!±CDATA±10012102981900828280161020115919»»></abcdefghi><abcdefghi id="ACD VFCGD"><!±CDATA±edrfs fdgfcd»»></abcdefghi><abcdefghi id="Reason for Removal"><!±CDATA±Needs Changes In Life»»></abcdefghi></ihgfedcba>
3) <?xml version = '1.0'?><ihgfedcba><abcdefghi id="idvMethod"><!±CDATA±N/A»»></abcdefghi><abcdefghi id="status"><!±CDATA±Success»»></abcdefghi><abcdefghi id="type"><!±CDATA±Update»»></abcdefghi><abcdefghi id="Outbound Indicator"><!±CDATA±FALSE»»></abcdefghi><abcdefghi id="bcdfe as"><!±CDATA±10020501901900820620161020150121»»></abcdefghi><abcdefghi id="ACD VFCGD"><!±CDATA±edrfs fdgfcd,ACD VFCGD cdvs»»></abcdefghi><abcdefghi id="Reason for Removal"><!±CDATA±Needs Changes In CITY»»></abcdefghi></ihgfedcba>
4) <?xml version = '1.0'?><ihgfedcba><abcdefghi id="idvMethod"><!±CDATA±N/A»»></abcdefghi><abcdefghi id="status"><!±CDATA±Success»»></abcdefghi><abcdefghi id="type"><!±CDATA±Update»»></abcdefghi><abcdefghi id="Outbound Indicator"><!±CDATA±FALSE»»></abcdefghi><abcdefghi id="bcdfe as"><!±CDATA±00101100221900855500161020102626»»></abcdefghi><abcdefghi id="ACD VFCGD"><!±CDATA±edrfs fdgfcd»»></abcdefghi><abcdefghi id="Reason for Removal"><!±CDATA±Needs Changes In ABCSDE TO TEST»»></abcdefghi></ihgfedcba>
5) <?xml version = '1.0'?><ihgfedcba><abcdefghi id="idvMethod"><!±CDATA±N/A»»></abcdefghi><abcdefghi id="status"><!±CDATA±Success»»></abcdefghi><abcdefghi id="type"><!±CDATA±Update»»></abcdefghi><abcdefghi id="Outbound Indicator"><!±CDATA±FALSE»»></abcdefghi><abcdefghi id="bcdfe as"><!±CDATA±10006102051900851200161020181050»»></abcdefghi><abcdefghi id="ACD VFCGD"><!±CDATA±ACD VFCGD cdvs»»></abcdefghi><abcdefghi id="Reason for Removal"><!±CDATA±Needs Changes In World»»></abcdefghi></ihgfedcba>
6) <?xml version = '1.0'?><abcdefghi><abcdefghi id="featureProdCode"><!±CDATA±2689»»></auditItem><auditItem id="weasefcswvtvsf"><!±CDATA±200000»»></abcdefghi><abcdefghi id="Achnrgescnueq"><!±CDATA±00291802»»></abcdefghi><abcdefghi id="type"><!±CDATA±Update»»></abcdefghi><abcdefghi id="status"><!±CDATA±Success»»></abcdefghi><abcdefghi id="idvMethod"><!±CDATA±N/A»»></abcdefghi><abcdefghi id="abcdefg hi"><!±CDATA±00101000181900022200161021191626»»></abcdefghi><abcdefghi id="Outbound Indicator"><!±CDATA±FALSE»»></abcdefghi></auditData>
7) <?xml version = '1.0'?><abcdefghi><abcdefghi id="featureProdCode"><!±CDATA±2682»»></abcdefghi><abcdefghi id="weasefcswvtvsf"><!±CDATA±202608»»></abcdefghi><abcdefghi id="Achnrgescnueq"><!±CDATA±12190881»»></abcdefghi><abcdefghi id="type"><!±CDATA±Update»»></abcdefghi><abcdefghi id="status"><!±CDATA±Success»»></abcdefghi><abcdefghi id="idvMethod"><!±CDATA±PINsentry»»></abcdefghi><abcdefghi id="MCA"><!±CDATA±TRUE»»></abcdefghi><abcdefghi id="PRP"><!±CDATA±FSV»»></abcdefghi><abcdefghi id="cdef hi"><!±CDATA±MAFTSP»»></abcdefghi></auditData>
8) <?xml version = '1.0'?><abcdefghi><abcdefghi id="featureProdCode"><!±CDATA±2689»»></abcdefghi><abcdefghi id="weasefcswvtvsf"><!±CDATA±209029»»></abcdefghi><abcdefghi id="Achnrgescnueq"><!±CDATA±12620222»»></abcdefghi><abcdefghi id="type"><!±CDATA±Update»»></abcdefghi><abcdefghi id="status"><!±CDATA±Success»»></abcdefghi><abcdefghi id="idvMethod"><!±CDATA±Alternative verification»»></abcdefghi><abcdefghi id="abcdefg hi"><!±CDATA±10000295881900020900161021161628»»></abcdefghi><abcdefghi id="Outbound Indicator"><!±CDATA±FALSE»»></abcdefghi></auditData>
9) <?xml version = '1.0'?><abcdefghi><abcdefghi id="featureProdCode"><!±CDATA±2689»»></abcdefghi><abcdefghi id="weasefcswvtvsf"><!±CDATA±206651»»></abcdefghi><abcdefghi id="Achnrgescnueq"><!±CDATA±60221206»»></abcdefghi><abcdefghi id="type"><!±CDATA±Update»»></abcdefghi><abcdefghi id="status"><!±CDATA±Success»»></abcdefghi><abcdefghi id="idvMethod"><!±CDATA±PINsentry»»></abcdefghi><abcdefghi id="MCA"><!±CDATA±TRUE»»></abcdefghi><abcdefghi id="PRP"><!±CDATA±FSV»»></abcdefghi><abcdefghi id="cdef hi"><!±CDATA±MAFTSP»»></abcdefghi></auditData>

 

Expected Out Come

 

Want to Test It Out
Needs Changes In Life
Needs Changes In CITY
Needs Changes In ABCSDE TO TEST
Needs Changes In World

Senior Apprentice

Re: substring() on huge text column

How is this failing, does it fail or is the result not correct?

 

You're not using ODBC-functions, thus it should run as-is in BTEQ, but you might have a different session character set (ASCII vs. UTF) and the '»' char might be affected by that.

Otherewise you can replace your SUBSTRING/POSITION with a simple RegEx, which extracts everything after the searched string up to the next '»':

 

RegExp_Substr(field1, '(?<=Reason for Removal"><!±CDATA±)[^»]*')
or
RegExp_Substr(field1, '(?<=Reason for Removal.{12})[^»]*')