Finding first alphabetic character in a DB2 database field

Question

I'm doing a bit of work which requires me to truncate DB2 character-based fields. Essentially, I need to discard all text which is found at or after the first alphabetic character.

e.g.

102048994BLAHBLAHBLAH

becomes:-

102048994

In SQL Server, this would be a doddle - PATINDEX would swoop in and save the day. Much celebration would ensue.

My problem is that I need to do this in DB2. Worse, the result needs to be used in a join query, also in DB2. I can't find an easy way to do this. Is there a PATINDEX equivalent in DB2?

Is there another way to solve this problem?

If need be, I'll hardcode 26 chained LOCATE functions to get my result, but if there is a better way, I am all ears.

Answer 1

hi,
write a small UDF (user defined function) in C or JAVA, that does your task.
Peter

Answer 2

This answer assumes you have an Auxiliary Numbers table with a sequence at least as large as the longest string. I have no idea how practical this is but might be worth a shot!

SELECT TESTING.PK, 
       VAL, 
       COALESCE(SUBSTR(Val, 1, FirstAlpha - 1),'') AS NonAlphaPrefix
FROM TESTING
LEFT OUTER JOIN
(
    SELECT PK, MIN(Numbers.Number) AS FirstAlpha
     FROM TESTING
        JOIN Numbers ON Numbers.Number <= LENGTH(Val) + 1 
    WHERE 
        (SUBSTR(Val, Numbers.Number, 1) >= 'A' 
            AND SUBSTR(Val, Numbers.Number, 1) <= 'Z')
    OR
        (SUBSTR(Val, Numbers.Number, 1) >= 'a' 
            AND SUBSTR(Val, Numbers.Number, 1) <= 'z')
    GROUP BY PK

)
g ON g.PK  = TESTING.PK

NB: I don't have DB2 to play with so wrote this in SQL Server using SUBSTRING and DATALENGTH. As far as I can see these are directly equivalent to the DB2's SUBSTR and LENGTH functions which I have substituted in. If not it may need tweaking.