473,321 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Help needed writing an SQL statement with parsing

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.
Feb 4 '08 #1
2 1494
r035198x
13,262 8TB
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.
Feb 5 '08 #2
docdiesel
297 Expert 100+
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
Feb 5 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
2
by: Birt | last post by:
1. In a program, there will be several files needed to be accessed. Is it necessary to design a class that handles reading/writing files? 2. For a struct data type, struct mystruct { int...
67
by: Steven T. Hatton | last post by:
Some people have suggested the desire for code completion and refined edit-time error detection are an indication of incompetence on the part of the programmer who wants such features. ...
22
by: Rafia Tapia | last post by:
Hi all This is what I have in mind and I will appreciate any suggestions. I am trying to create a xml help system for my application. The schema of the xml file will be <helpsystem> <help...
6
by: GrandpaB | last post by:
While writing this plea for help, I think I solved my dilemma, but I don't know why the problem solving statement is necessary. The inspiration for the statement came from an undocumented VB...
2
by: clinttoris | last post by:
Hello, If someone could help me it would be appreciated as I am not having much luck. I'm struggling with my asp code and have some questions relating to asp and oracle database. First...
15
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
118
by: Chuck Cheeze | last post by:
This might be in the wrong group, but... Here is an example of my data: entry_id cat_id 1 20 2 25 3 30 4 25 5 35
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.