Anyone could help with this question? I want to select part of a string. The string is like abc_def_dfsfs_dfdfasdfs_fsdfs_fbc_fsd_sfs_sdfsd.
I want to select fbc and fsd, but the issue is the number of characters before is not costant. For example, before fbc, there could be a 7 character string as well. I think the thing I should use to position is '_', but there are like 7 underscores in the whole string. How can I do it?
Thanks for any advice!
You can try recursive view to search for 5th and 6th '_' and then substring from there to next '_'.
What's your TD release?
In TD14 there's INSTR to find the nth occurence of a string or better STRTOK to extract the nth token from a delimited string.
Previously you might check if INSTR (or a similar UDF) is installed on your system.
Dieter, thanks for your advice as well! But unfortunately, mine is TD13, which does not have INSTR or STRTOK functions...
Could anyone provide the syntax of recursive view please? I was trying to search for it online, but could not find it.
An example could be i want to find out the 4th '_' in a field called web_name.
You can modify the query i posted at
WITH RECURSIVE cte
) AS (
POSITION('_' IN String || '_') - 1 AS len,
SUBSTRING(String || '_' FROM len + 2) AS remaining,
SUBSTRING(String FROM 1 FOR len) AS word,
POSITION('_' IN remaining)- 1 AS len_new,
SUBSTRING(remaining FROM len_new + 2),
SUBSTRING(remaining FROM 1 FOR len_new),
pos + 1
WHERE remaining <> ''
AND pos <= 4
WHERE pos = 4
But a UDF is still the best solution, you might ask your DBA to install one. Most DBA don't like UDFs as they are written in a unknown language (C) and the Oracle functions (http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions) are not officially supported, but when you switch to TD14 they will be included and you could start using them right now.
Or use those ebay functions found at http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs, they're definitely tested a lot and probably use the fastest possible code.
Sorry for jumping in.
You can also get the results using an 'iterator' :
As per your example (4th ocurrence of '_':
BTEQ -- Enter your SQL request or BTEQ command:
SELECT SUBSTR(pre.cadena, it.iterador, 1) caracter,
FROM ( SELECT '1_3_5_7_9_ABCDEF' cadena ) pre,
( SELECT day_of_calendar iterador FROM SYS_CALENDAR.CALENDAR WHERE DAY_OF_CALENDAR < 16) it
QUALIFY ROW_NUMBER() OVER (ORDER BY posicion) = 4
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.
no excuses, you're welcome :-)
Of course you can use a cross join to a number table to split a string, but performance mainly depends on the defined size of the VARCHAR - cross joining a large number of rows to a few hundred/thousand rows consumes a ot of CPU. I tested several variations of cross joins/recursion some years ago and none was even close to a DelimitedToVarcharTbl UDF. And the new STRTOK consumes less CPU than that.
Btw, this query only extract the position of the underscore you still need to extract the following word :-)
SUBSTR(pre.cadena, it.iterador+1, POSITION('_' IN SUBSTR(pre.cadena || '_', it.iterador+1))-1) AS word
Agreed, but I was only giving another choice to the OP. I don't know the requirements and varchar sizes, and some people find it hard to deal with recursives...
The OP wanted "find out the 4th '_'" and I only showed how to do it with only one string for didactical purposes ("don't give a fish, show how to fish...")
Anyway, the long-waited 'native' strtok() is, imho, the best solution, of course.