Finding first alphabetic character in a DB2 database field

Posted by Paul Alan Taylor on Stack Overflow See other posts from Stack Overflow or by Paul Alan Taylor
Published on 2010-04-29T12:53:10Z Indexed on 2010/04/29 12:57 UTC
Read the original article Hit count: 591

Filed under:

db2

|

patindex

|

database-queries

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.

© Stack Overflow or respective owner

Related posts about db2

link DB2 AS/400 (system i) and DB2 AIX

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, everybody. I have two different database in DB2, a in DB2 AS400 and other in DB2 AIX, is possible link this database, similar to link by Oracle? Thank you in advance. erva. >>> More
Speed-start your Linux App: Using DB2 and the DB2 Control Center

as seen on Internet.com - Search for 'Internet.com'
This article guides you through setting up and using IBM DB2 7.2 with the Command Line Processor. You'll also learn to use the graphical Control Center, which helps you explore and control your databases, and the graphical Command Center, which helps you generate SQL queries. Other topics covered… >>> More
DB2 LUW Security - The DB2 DBA as a Locksmith

as seen on Internet.com - Search for 'Internet.com'
Database security should have multiple locks at multiple layers with multiple keys (and perhaps some barbed wire, an electric fence, a moat and a mean junkyard dog as well). With all these locks and keys, some locksmith skills are certainly useful. Rebecca Bond introduces some of her favorite DB2… >>> More
Why can't Perl's DBD::DB2 find dbivport.h during installation?

as seen on Stack Overflow - Search for 'Stack Overflow'
We are using a Perl utility to dump data from DB2 database. We installed DBI package and it is asking for DBD package also. We dont have root access and when we try to install DBD package we are getting the following error: ERROR BUILDING DB2.pm [lijumathew@intblade03 DBD-DB2-1.78]$ make make[1]:… >>> More
DBI::DBD package not getting installed for Perl?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, We are using a Perl utility to dump data from DB2 database. We installed DBI package and it is asking for DBD package also. We dont have root access and when we try to install DBD package we are getting the following error. ERROR BUILDING DB2.pm [lijumathew@intblade03 DBD-DB2-1.78]$ make make[1]:… >>> More

Related posts about patindex

Find the occurrence of word/character in SQL column with wildcard character - PATINDEX

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
CharIndex and PatIndex both can be used to determine the presence of character or string within sql column data. Both returns the starting position of the first occurrence of the character/word within expression. However, one major difference between CharIndex and PatIndex is that later allows the… >>> More
SQL SERVER – Parsing SSIS Catalog Messages – Notes from the Field #030

as seen on SQL Authority - Search for 'SQL Authority'
[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications. The tool may also be used to automate… >>> More
HSSFS Part 2.1 - Parsing @@VERSION

as seen on SQL Team - Search for 'SQL Team'
For Part 2 of the Handy SQL Server Function Series I decided to tackle parsing useful information from the @@VERSION function, because I am an idiot. It turns out I was confused about CHARINDEX() vs. PATINDEX() and it pretty much invalidated my original solution. All is not lost though, this mistake… >>> More
SQL SERVER – SSMS: Database Consistency History Report

as seen on SQL Authority - Search for 'SQL Authority'
Doctor and Database The last place I like to visit is always a hospital. With the monsoon season starting, intermittent rains, it has become sort of a routine to get a cycle of fever every other year (seriously I hate it). So when I visit my doctor, it is always interesting in the way he quizzes me… >>> More
oracle equivalent of patindex and instr function with wild card characters

as seen on Stack Overflow - Search for 'Stack Overflow'
is there an oracle equivalent of patindex? from my search, the only function that is close to patindex is oracle's instr function but it does not support wild card. what is the oracle equivalent of the following query? select patindex('%[^0]%','00194505022') >>> More