0

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.

Paul Alan Taylor
  • 10,474
  • 1
  • 26
  • 42

2 Answers2

1
SELECT TRANSLATE(lower(column), ' ', 'abcdefghijklmnopqrstuvwxyz') 
FROM table
Stradivariuz
  • 2,523
  • 1
  • 18
  • 6
0

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

Peter Miehle
  • 5,984
  • 2
  • 38
  • 55