Help needed writing an SQL statement with parsing

[ckreisler]

I am trying to write an SQL statement that will inspect one column of a table and return only the first letter of each word in the column.

For intance : The little red hen crossed the road

The reults would be : Tlrhctr.

Don't even ask why this is needed, our customer service department is trying to set up alternate keys for product lookup, and if we built a cross reference file that matched back to our Product Master, it would help in finding products.

Not even sure if this is possible, Thank you in advance fro anything you might be able to share on this.

[r035198x]

I am trying to write an SQL statement that will inspect one column of a table and return only the first letter of each word in the column.

For intance : The little red hen crossed the road

The reults would be : Tlrhctr.

Don't even ask why this is needed, our customer service department is trying to set up alternate keys for product lookup, and if we built a cross reference file that matched back to our Product Master, it would help in finding products.

Not even sure if this is possible, Thank you in advance fro anything you might be able to share on this.

Have a look at the SUBSTR and CONCAT functions.

[docdiesel]

Hi,

for that the number of words will be different from row to row, you'll need some recursive function. Unfortunately, in DB2 there's nothing builtin like the Split() function of Perl. Seems like you'll have to build an UDF on your own, using PosStr() and SubStr().

But maybe there's another way. I'm not sure what your CS dept. wants (or needs; not always the same), but maybe the function SOUNDEX() is uselful to you. It returns a 4-character code representing the sound of the words in the argument. Another one is DIFFERENCE(), which returns a value from 0 to 4 representing the difference between the sounds of two strings based on applying the SOUNDEX() function to the strings. Maybe one of these could help you. Have a look at the DB2 SQL reference for details.

Regards,

Bernd