How do you convert a regexp_instr of oracle in teradata

Database
Fan

How do you convert a regexp_instr of oracle in teradata

I need to conver the regexp_instr being used in my query code to teradata. I ahve tried many but they dont work exactly like regexp_instr.. an soem body please suggest me some thing. i acnnot use UDF's

I have tried 

and regexp_instr(drug.CODE,''[^[:digit:]]'',1,1) = 0   /* The actual one of oracle*/

/* add to avoid invalid number due to junk code */

1. AND (CASE when (drug.code) like '%[0-9]%' then 1 else 0 end) = 0 

2. AND (CASE WHEN (POSITION(' ' IN (drug.code)) > 0) 

OR (UPPER((drug.code)) (CASESPECIFIC) <> LOWER((drug.code)) (CASESPECIFIC)) 

 THEN 1 ELSE 0 end ) = 0 

3. and  POSITION(' '  IN (code))  = 0  order by code) 

but none of these do the exact work that I am looking into.. No UDF plzz  

2 REPLIES
Enthusiast

Re: How do you convert a regexp_instr of oracle in teradata

The following SQL test can be done without having to create tables as you alluded in your StackOverflow question creating tables was not possible. This test yielded 1 for both statements.

SELECT (CASE WHEN (POSITION('' '' IN TRIM('1234')) > 0) OR (UPPER(TRIM('1234'))              
(CASESPECIFIC) <> LOWER(TRIM('1234')) (CASESPECIFIC))
THEN 1 ELSE 0 END )

SELECT (CASE WHEN (POSITION('' '' IN TRIM('abcd ef1')) > 0) OR (UPPER(TRIM('abcd ef1'))
(CASESPECIFIC) <> LOWER(TRIM('abcd ef1')) (CASESPECIFIC))
THEN 1 ELSE 0 END )

Trying to parse a string in the manner in which you would like without the use of a native or user defined function is going to be difficult at best and impossible at worst. One of the features in Teradata 14, that was recently announced at Partners, is the native support for regular expressions in several functions. One of which would likely solve the problem you have described.

It wouldn't be difficult to build a UDF that wraps the C isdigit function. (http://www.cplusplus.com/reference/clibrary/cctype/isdigit/)

Fan

Re: How do you convert a regexp_instr of oracle in teradata

This is exactly what I had tried but it dosent work in all the conditions. I was trying for something that gives me only those rows which have numbers and no other characters. I am not supposed to write UDF's as I dont have the access rights. 

Thanks anyways..