Find the first occurance of Number in a string

Database
Enthusiast

Find the first occurance of Number in a string

Hi All,

I want to find the first occurance of number [0-9] in a particular string.
Say for an example

Input : ABCDE5789PDH Output : 6
Input : DE09567 Output : 3

Total String width 12

We can think of only way to achive this is using case when 12 times

case when substr(ABCDE5789PDH,1,1) between 0 and 9 then 1
when substr(ABCDE5789PDH,2,1) between 0 and 9 then 2
.
.
.
end

but worried this will cause performance bottleneck for millions of records.

Is there any otherway to achive apart from using UDF (regexp).

Thanks

3 REPLIES
Enthusiast

Re: Find the first occurance of Number in a string

Hi, I've got some idea

We know that numeric characters have a values from 0030-0039 in hexadecimal code(UTF8) - i checked this on teradata (windows an unix platforms).
So my idea is to convert Your string into hex and then search for '003' value.

select ( (position('003' in char2hexint('ABCDE5789PDH')) -1 ) / 4) +1 --returns 6
select ( (position('003' in char2hexint('DE09567')) -1 ) / 4) +1 --returns 3

some calculation are needed
-1 - number of characters preceding our string in hex string
/4 we have to divide it to get the real number of characters preceding
+1 next position is the real position of our character

Check this and write does it ok

cheers

Re: Find the first occurance of Number in a string

Thanks Kostek,

This works if you manually insert the string then it will return the HEX version properly with all 00 at the start however I realised if you have to read the string from the DB table then result will drop the 00 from all HEX conversions, so I had to use following in order to get that working

select ((POSITION( '3' IN CHAR2HEXINT(TRANSLATE( column1 USING UNICODE_TO_LATIN) ))/2)+1) from table1 where column1='ABCDE5789PDH'  --returns 6

FYI:

From Teradata documents

The following statement returns the hexadecimal representation of the string in column1:

SELECT CHAR2HEXINT(column1);

The result is '61', the hexadecimal representation of 'a' in the LATIN server character set, 

which uses one byte per character.

The following query compares the two strings:

SELECT column2 FROM table1

WHERE CHAR2HEXINT('a') = CHAR2HEXINT(column1);

The result is an empty set, because the result of CHAR2HEXINT('a') is '0061' and the result of 

CHAR2HEXINT(column1) is '61'. 

For situations such as this, you can use TRANSLATE to convert the server character set of one 

of the character strings to match the server character set of the other character string. For 

example:

SELECT column2 FROM table1

WHERE CHAR2HEXINT( TRANSLATE('a' USING UNICODE_TO_LATIN) ) =

CHAR2HEXINT(column1);

returns:

column2

-----------

1001

Regards

Enthusiast

Re: Find the first occurance of Number in a string

if the first character is C followed by a number, for example C23, the hexa decimal value of C is 43 and you will end up getting wrong results if you use the above logic