Re: Parsing a string in a column

Can't do that in SQL. You'll have to go with awk or an ESQL/C program. Art S. Kagel bumbershoot@centurytel.net wrote: > > Greetings to all SQL guru's. > > System details: Digital UNIX 4.0E - IDS 7.31FC4 > > In order to do some analysis on some data, I need to extract text from > one field and insert that into another table. I can unload data to a > text file and use Awk to parse it, then load it into the destination > table for analysis, but I'd rather find a SQL method if possible. > > Here's a sample: > > ...|307|1|random length string -12298 more random text|... > ^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^ > strip this out this too! > > ...|105|15|shorter string 243 more stuff here|... > ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^ > strip this out this too! > > What I'm trying to do is parse out everything from the text string except > the signed numeric values. I can get the job done with the IDS String > manipulation functions (substr, replace and the like) but I can only > manage it by running a procedure over and over against a single record, > and I've got to do this on approx. 90,000 records daily. Phew. So far, > Awk is far preferable to what I've managed. > > Is there any reasonable way to accomplish this using SQL, or should I > stick with the unload - Awk - load process? > > Thanks for your time in advance! > > Brad Kittredge > Data Analyst > brad.kittredge@metrokc.gov