VFP 6 database field query 6

[Phil Thoms]

Hello,
I have a large database file with a char field containing codes for example; AB123, ABC567 etc. I need to separate the letters from the numbers by inserting a space. Is there a quick method for achieving this?

Thanks for your help.

 

[Mike Lewis]

Do the codes always consist of a series of letters followed by a series of digits? If so, then something like this:

lcStr = ... && contents of the database field
lnCount = 0
FOR lnI = 1 TO LEN(lcStr)
  IF ISALPHA(SUBSTR(lcStr, lnI, 1))
    lnCount = lnCount + 1
  ELSE
    EXIT
ENDFOR

lcResult = SUBSTR(lcStr, 1, lnCount) + " " + SUBSTR(lcStr, lnCount + 1)

This is off the top of my head, and has not been tested, but it should give you a general idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Phil Thoms]

Hello,
In reply to Mike, yes the codes always consist of letters first followed by numbers. I understand the code you have given so would you then use a replace command?

Thanks

 

[Olaf Doschke]

For avoiding data loss you'll need to increase the width of the field by 1, so no char is trimmed at the right.
Stuff() should be able to do it. Stuff(yourfield,place of first digit, 0, " ") && insert a space at the position of the first digit, because 0 characters replaced means the rest is shifted, the space is inserted at that position.

The place of first digit is as simple as the length+1 after removing all digits (in memory, not in the field), eg LEN(RTRIM(CHRTRAN(yourfield,"0123456789","")))+1

So overall (first make a copy):

ALTER TABLE yourtable ALTER yourfield char(N+1) && where N is the current field width
UPDATE yourtable SET yourfield = Stuff(yourfield,LEN(RTRIM(CHRTRAN(yourfield,"0123456789","")))+1, 0, " ")

If you have a code like AB12Z6, though, that would result in AB1 2Z6, as three chars are not digits.

Bye, Olaf.

 

[Mike Lewis]

The easiest way would be to make my code into a function, like this:

FUNCTION AddSpace
LPARAMETERS lcStr 
lnCount = 0
FOR lnI = 1 TO LEN(lcStr)
  IF ISALPHA(SUBSTR(lcStr, lnI, 1))
    lnCount = lnCount + 1
  ELSE
    EXIT
ENDFOR

RETURN SUBSTR(lcStr, 1, lnCount) + " " + SUBSTR(lcStr, lnCount + 1) 
ENDFUNC

You can then replace all the fields in the table in one shot, like this:

SELECT TheTable
REPLACE ALL Code WITH AddSpace(Code)

Of course, you would have to make sure that the field in question is wide enough to hold the extra character.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

If it's always 3 letters, then Stuff(yourfield,4,0," ") will insert a space at position 4. Your example of AB123 then is a typo.

Bye, Olaf.

 

[Phil Thoms]

Thanks Mike and Olaf, It's not always 3 letters can be 2 or 3 always followed by 3 or 4 numerals, no letters occur again.

 

[Olaf Doschke]

Well, then the more complex Stuff from my side, or use Mikes suggestion. Don't forget you can shift a digit out of the field, if you don't widen it first.

Bye, Olaf.

 

[vgulielmus]

Because it's a huge table, I made a small variation of Olaf Doschke's solution:

UPDATE yourtable SET cc=STUFF(yourfield,LEN(GETWORDNUM(yourtable.yourfield,1,"0123456789"))+1,0," ")

RAND(-1)
CLOSE DATABASES ALL 
CREATE TABLE bb (cc C(20))
FOR lni=1 TO 10000000
	nChar=2+INT(5*RAND())
	lcString=''
	FOR lnj=1 TO m.nChar
		lcString=m.lcString+CHR(65+INT(25*RAND()))
	NEXT
	nNum=2+INT(5*RAND())
	FOR lnj=1 TO m.nNum
		lcString=m.lcString+CHR(48+INT(9*RAND()))
	NEXT
	INSERT INTO bb VALUES (m.lcString)
NEXT

SELECT * FROM bb INTO CURSOR aa READWRITE
tt=SECONDS()
UPDATE aa SET cc=STUFF(cc,LEN(GETWORDNUM(aa.cc,1,"0123456789"))+1,0," ")
?SECONDS()-tt
BROWSE

SELECT * FROM bb INTO CURSOR aa READWRITE
tt=SECONDS()
UPDATE aa SET cc = Stuff(cc,LEN(RTRIM(CHRTRAN(cc,"0123456789","")))+1, 0, " ") 
?SECONDS()-tt
BROWSE

SELECT * FROM bb INTO CURSOR aa READWRITE
tt=SECONDS()
SELECT aa
REPLACE ALL cc WITH AddSpace(cc) 
?SECONDS()-tt
BROWSE



FUNCTION AddSpace
LPARAMETERS lcStr 
lnCount = 0
FOR lnI = 1 TO LEN(lcStr)
  IF ISALPHA(SUBSTR(lcStr, lnI, 1))
    lnCount = lnCount + 1
  ELSE
    EXIT
  ENDIF
ENDFOR

RETURN SUBSTR(lcStr, 1, lnCount) + " " + SUBSTR(lcStr, lnCount + 1) 
ENDFUNC

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Good idea, no need to remove digits, simply take them as word delimiter.

For VFP6 you'll need to SET LIBRARY TO foxtools.Fll before you have GETWORDNUM available. It was put into the native set of functions later.

Bye, Olaf.

 

[vgulielmus]

I must create a second cont here, to give you more than a single star

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Phil Thoms]

I've tried Mike's way but it doesn't work. Stays on record no.1.
Not yet had time to try the other method.

Thanks

 

[Mike Lewis]

I've tried Mike's way but it doesn't work. Stays on record no.1.

The AddSpace() function does not move the record pointer. In fact, it's got nothing to do with records or with the table.

It's the REPLACE that actually does the updating. And it's the keyword ALL that tells it to replace every record. After the command has finished, the table should be at end of file. Did you perhaps forget to include ALL?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

As Mike says.

Vilhelm-Ion Praisach has provided good sample code, acting on sample data he generates with random values. The essential line is REPLACE ALL field WITH AddSpace(field)
Or you do an UPDATE.

Bye, Olaf

 

[Phil Thoms]

Hello Mike and Olaf, the word ALL is in the replace command as per your code.
Thanks

 

[Olaf Doschke]

Hello Phil,

how did you put this together? Do you get any error message?

Vilhelm-Ion Praisach has used Mikes function 1:1 as posted and it has used the REPLACE ALL, and it works. So you copied something wrong or didn't execute it. The code is OK.

Bye, Olaf.

 

[Phil Thoms]

Apologies, I did copy code wrong. It now works fine and I'll keep notes of all the code that was put forward. Thanks for all your efforts, very much appreciated.

 

[mmerlinn]

Based on your examples something like this would also work:

[tt]text = "abc123"
result = ""

DO WHILE NOT EMPTY(text)

[tt]IF ISALPHA(text)

[tt]result = result + LEFT(text, 1)
text = SUBSTR(text, 2)[/tt]​

ELSE

[tt]result = result + " " + text
text = ""[/tt]​

ENDIF[/tt]​

ENDDO[/tt]​

This will work as long as all letter(s) in the original text are before the numeral(s), regardless of how many there are of each in the text.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond