String Processing in Queries 
Author Message
 String Processing in Queries

Here is a question about string processing in queries.  The last guy who worked
on the database here decided to include what should be two fields in one field.
What I want to do is divide an alphanumeric field of two characters into two
fields of one each.  Sounds simple, but I can't figure out any simple way to do
it with a query.

Am I right that PAL functions such as SUBSTR cannot be used in queries? (If not
, I would also be interested in knowing why Borland did not design such an
ability into the query system.)

Any ideas?

Mark Brand



Sat, 23 Nov 1996 06:50:06 GMT
 String Processing in Queries

Quote:

>Here is a question about string processing in queries.  The last guy who worked
>on the database here decided to include what should be two fields in one field.
>What I want to do is divide an alphanumeric field of two characters into two
>fields of one each.  Sounds simple, but I can't figure out any simple way to do
>it with a query.
>Am I right that PAL functions such as SUBSTR cannot be used in queries? (If not
>, I would also be interested in knowing why Borland did not design such an
>ability into the query system.)

You are right that PAL functions cannot be used in queries.

Two possible reasons why Borland did not include SUBSTR in "the query system".

The query engine that Paradox uses, is a QBE based engine.  QBE was not
invented by Borland or even ANSA (people who developed the first version of
Paradox). QBE was invented IBM.  And there are actually other databases on the
market that use QBE.  Standard QBE does have a 'substring' statement, and in    
fact most query languages (not including database languages) don't either.  So
the Paradox query engine was probably designed to keep with the standards.

Why do almost all query languages not have a 'substring' command?

In Relation Data Model Theory each field is a set of atomic values, meaning
that values defined by field are indivisible.  Since most modern query
languages are based on this theory, they would follow this fundamental rule.
Currently companies such as ORACLE are working on more advanced query
engines that actually search through fields for English language
recognition.

So the solution to your problem is use a scan loop.

COEDIT "TblName"
SCAN :
  [First Half] = SUBSTR([OrigFld],1,1)
  [Second Half] = SUBSTR([OrigFld],2,1)
ENDSCAN
DO_IT!

--

||   || ||\/|| ||=||   |||     Phone : (408) 425-7455
!!!! !! !!  !! !! !! !!   !!   Fax   : (408) 425-7516



Sat, 23 Nov 1996 13:14:17 GMT
 String Processing in Queries
: Here is a question about string processing in queries.  The last guy who worked
: on the database here decided to include what should be two fields in one field.
: What I want to do is divide an alphanumeric field of two characters into two
: fields of one each.  Sounds simple, but I can't figure out any simple way to do
: it with a query.

: Am I right that PAL functions such as SUBSTR cannot be used in queries? (If not
: , I would also be interested in knowing why Borland did not design such an
: ability into the query system.)

: Any ideas?

: Mark Brand

Mark

If you are using PDOXDOS, you cannot use substr etc in queries (dunno why
either<g>), but you can achieve what you want by using SCAN, which skips
records one by one and makes the required alterations.

I would suggest using SEARCH, if you know of a character or characters that
clearly define the breakpoint that you require. You won;t need that if the
first and last parts are of a given width, however.

I have posted some code that may help, if you're not a PALler. If I'm acting
redundant, then ignore.

PDOXDOS

;TABLE1 = sourcetable
;TABLE2 = NewTable
;FTBB = FieldToBeBroken
;RF1 = ReceivingFieldOne
;RF2 = ReceivingFieldTwo

;routine starts

BreakString = " "
;the point at which we wish to split the field

if not isempty( "TABLE2" ) then empty "TABLE2" endif

view "TABLE2"
view "TABLE1"

coeditkey
scan            ;table1 scan

    F1Var = substr( [FTBB],1,search( BreakString,[FTBB] ) - 1 )

    ;from first char to first occurrence of breakpoint

    F2Var = substr( [FTBB], search ( BreakString,[FTBB] ) + 1, len( [FTBB] ) )
    ;the rest. If we have more than one occurrence of "breakstring" (eg
    ; two or more spaces) then the "search" part of this equation should
    ; be altered accordingly, or for actual repetitiin, BreakString could
    ; be redefined
    ;we now have variable for each part

    moveto "TABLE2"

    end down    ;add new record

    [RF1] = F1Var
    [RF2] = F2Var

    moveto "Table1"

endscan

clearall

routine ends
You now have TABLE2 with the broken fields

This is not as fast as a query, but you're only doing it once.

Note: If any of the TABLE1 records contains only one part of the two parts to
 be broken, this routine will plonk it into one or other of the fields in
 TABLE2. This would be found by SCAN ning TABLE2 for isblank([RF1]) or
 isblank( [RF2] ), and a=getchar() (wait for key input) if any record is
 blank in either field. You can then note the record number.

Hope this helps. Works for me.

Nick White



Sun, 24 Nov 1996 08:54:52 GMT
 String Processing in Queries

IB>Here is a question about string processing in queries.  The last guy who
IB>worked
IB>on the database here decided to include what should be two fields in one
IB>field.
IB>What I want to do is divide an alphanumeric field of two characters into two
IB>fields of one each.  Sounds simple, but I can't figure out any simple way to
IB>do
IB>it with a query.

IB>Am I right that PAL functions such as SUBSTR cannot be used in queries? (If
IB>not
IB>, I would also be interested in knowing why Borland did not design such an
IB>ability into the query system.)

IB>Any ideas?

IB>Mark Brand

Here's a quick script to do it:
FIELD1 is the field containing the old info with 2 letters.
FIELD2 is the field you should have created in the table for the second
character.

EDIT "TABLENAME"
SCAN
[FIELD1] = SUBSTR([FIELD1],1,1)
[FIELD2] = SUBSTR([FIELD1],2,1)
ENDSCAN
DO_IT!

Ashar

BAYLINE BBS



Mon, 25 Nov 1996 04:52:09 GMT
 String Processing in Queries
You are correct that SUBSTR is not supported within queries.  Instead, you have
to use it within a SCAN loop in a script.  I too have often wondered why we
can't do this sort of thing in a query, but this is what we've got.
+==========================================================+
|     /\  ~~                   Ernie DeVries               |
| ~~ /  \/\      End User Computing & Information Services |

|   Northern Arizona University   "The Mountain Campus"    |
+==========================================================+


Wed, 27 Nov 1996 00:20:07 GMT
 String Processing in Queries

Quote:

> You are correct that SUBSTR is not supported within queries.  Instead, you have
> to use it within a SCAN loop in a script.  I too have often wondered why we
> can't do this sort of thing in a query, but this is what we've got.
> +==========================================================+

I am sure it seems counter intuitive to many new Paradox users such as myself
that the concatenation operator "+" can be used in a query to
combine two fields into one but that there is no way to undo
this in a query.

Sure would be nice if QBE and paradox were sufficiently integrated so that
functions could be used in queries. (And variables for table names for that
matter!).  In spite of these infelicities, I like paradox.

MB



Wed, 27 Nov 1996 02:01:16 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. STRING PROCESSING IN QUERIES

2. ORA-04030 out of process memory when trying to allocate string bytes (string,string)

3. Parse ASP search string into query string?

4. P5W: table string like query string??

5. query processing and query evaluation

6. query processing, query optimization

7. Stored Parameter Query vs runtime query string

8. Query vars Vs Query string

9. queries query- strings with wildcards

10. How do we know if a process is user process or system process

11. string processing in user-defined scalar function

12. help: string process in PL/SQL


 
Powered by phpBB® Forum Software