Find position of a character in a string

Database
Enthusiast

Find position of a character in a string

Hi Everyone,

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!

15 REPLIES
Enthusiast

Re: Find position of a character in a string

Hi,

You can try recursive view to search for 5th and 6th '_' and then substring from there to next '_'.

Thanks

Junior Contributor

Re: Find position of a character in a string

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

Enthusiast

Re: Find position of a character in a string

Thanks Harpreet! I would use that.

Enthusiast

Re: Find position of a character in a string

Dieter, thanks for your advice as well! But unfortunately, mine is TD13, which does not have INSTR or STRTOK functions...

Enthusiast

Re: Find position of a character in a string

Hi Everyone, 

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.

Thanks!

Junior Contributor

Re: Find position of a character in a string

You can modify the query i posted at

http://forums.teradata.com/forum/database/stored-procedures-need-help-with-massaging-data-within-an-...

WITH RECURSIVE cte
(groupcol,
len,
remaining,
word,
pos
) AS (
SELECT
GroupCol,
POSITION('_' IN String || '_') - 1 AS len,
SUBSTRING(String || '_' FROM len + 2) AS remaining,
SUBSTRING(String FROM 1 FOR len) AS word,
1
FROM strings
UNION ALL
SELECT
GroupCol,
POSITION('_' IN remaining)- 1 AS len_new,
SUBSTRING(remaining FROM len_new + 2),
SUBSTRING(remaining FROM 1 FOR len_new),
pos + 1
FROM cte
WHERE remaining <> ''
AND pos <= 4
)
SELECT
groupcol,
pos,
word
FROM cte
WHERE pos = 4
ORDER BY
groupcol, pos;

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.

Dieter

Dieter

Junior Supporter

Re: Find position of a character in a string

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,

       it.iterador posicion

  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

 WHERE caracter='_'

 QUALIFY ROW_NUMBER() OVER (ORDER BY posicion) = 4

 ;

 *** Query completed. One row found. 2 columns returned.

 *** Total elapsed time was 1 second.

caracter     posicion

--------  -----------

_                   8

HTH.

Cheers.

Carlos.

Junior Contributor

Re: Find position of a character in a string

Hi Carlos,

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

Dieter

Junior Supporter

Re: Find position of a character in a string

Dieter:

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.

Cheers.

Carlos.