Howto : Extract text from a field using space as delimiter 
Author Message
 Howto : Extract text from a field using space as delimiter

Hi everybody,

I am transfer an old dbf-tables to sql. There are one name-field, which
contain both firstname, some middlename and finally surename.
The problem is that the users type in the names with no commas separation.

The question is : how to extract part of the stringfield, where the criteria
is the space number x, depends upon how many first, and/or middlename thee
are in the record ?

I tried to use SELECT LEFT(nameField,AT(' ',nameField)) from myTable to
extract the first word from the string. The result is surprisingly (!) ,
only the 4 first letter of the first word occurs ...!?

I also tried to find a similar command as the REVERSE in standard SQL, but
there seems not to be anything in foxpro SQL. Is this correct ? (I want to
use it to detect the last word in the record....)

regards
Tri



Sun, 09 Mar 2003 03:00:00 GMT  
 Howto : Extract text from a field using space as delimiter


| Hi everybody,
|
| I am transfer an old dbf-tables to sql. There are one name-field, which
| contain both firstname, some middlename and finally surename.
| The problem is that the users type in the names with no commas separation.
|
| The question is : how to extract part of the stringfield, where the
criteria
| is the space number x, depends upon how many first, and/or middlename thee
| are in the record ?
|
| I tried to use SELECT LEFT(nameField,AT(' ',nameField)) from myTable to
| extract the first word from the string. The result is surprisingly (!) ,
| only the 4 first letter of the first word occurs ...!?

That's because the resulting expression of the first record is 4 chars long
try: PADR(left(namefield, at(' ',namefield)-1), 100)

| I also tried to find a similar command as the REVERSE in standard SQL, but
| there seems not to be anything in foxpro SQL. Is this correct ? (I want to
| use it to detect the last word in the record....)
|
| regards
| Tri
|
|



Sun, 09 Mar 2003 03:00:00 GMT  
 Howto : Extract text from a field using space as delimiter

Thanks a lot Gregory !!
It works !

One more thing : Do you know how to reverse a string ? Or do I have to write
the code manually ?

regards
Tri


Quote:



> | Hi everybody,
> |
> | I am transfer an old dbf-tables to sql. There are one name-field, which
> | contain both firstname, some middlename and finally surename.
> | The problem is that the users type in the names with no commas
separation.
> |
> | The question is : how to extract part of the stringfield, where the
> criteria
> | is the space number x, depends upon how many first, and/or middlename
thee
> | are in the record ?
> |
> | I tried to use SELECT LEFT(nameField,AT(' ',nameField)) from myTable to
> | extract the first word from the string. The result is surprisingly (!) ,
> | only the 4 first letter of the first word occurs ...!?

> That's because the resulting expression of the first record is 4 chars
long
> try: PADR(left(namefield, at(' ',namefield)-1), 100)

> | I also tried to find a similar command as the REVERSE in standard SQL,
but
> | there seems not to be anything in foxpro SQL. Is this correct ? (I want
to
> | use it to detect the last word in the record....)
> |
> | regards
> | Tri
> |
> |



Mon, 10 Mar 2003 03:00:00 GMT  
 Howto : Extract text from a field using space as delimiter
Tri,

There may be solutions around I'm not aware of.  If I were you I would write
my own.  It's really easy.
Somthething like that (untested)
--
Funtion ReverseString (s, sep)
    local i, n, aa[1], out
    if( sep = '' )
        sep = space(1) && or ','
    endif


    out = ''
    for i = n to 1 step -1
        out = out + iif(out<> '', sep, '') + aa[i]
    endfor
    return out
endfunc
------------------
function StringToArray(cStr, cA, cSep)    && one of my first
 local n, i, p, j, l

 cSep = iif( parameters() > 2, cSep, ',' )
 l = len(cSep)
 cStr = cStr + cSep
 n = occurs(cSep, cStr)
 dimension cA[n]
 p = 1

 for i = 1 to n
  j = at(cSep,cStr, i)
  cA[i] = substr(cStr,p, j-p)
  p = j + l
 endfor

 return n
endfunc
*---------------------

| Thanks a lot Gregory !!
| It works !
|
| One more thing : Do you know how to reverse a string ? Or do I have to
write
| the code manually ?
|
| regards
| Tri



Mon, 10 Mar 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. VERY URGENT: Importing Fields using delimiters

2. VERY URGENT: Importing Fields using delimiters

3. Spaces in text fields??

4. Method to remove spaces from text fields

5. HELP: Adjusting text spacing for field in Report Writer

6. Extract data from delimited text file

7. Extracting text from a memofield in a SQL command

8. howto calculated autoincremented field in sql query?

9. How To Extract Microsoft Visual FoxPro Index Expression Using ADO

10. Extract Worksheet from Workbook of Excel using Foxpro

11. How can I extract info from memo field?

12. Extracting 12 hr time from Datetime field

 

 
Powered by phpBB® Forum Software