separating 1 string of characters to 3 strings

[blumash]

I have a file with a field containing first middle & surname together I would like to separate them to 3 fields , first middle & last name , looking for the best solutaion.

Thanks

 

[TheRambler]

Hi,

There has been a lot of discussion about this subject, see thread184-567614, Name Parsing.

You will find a file to download if you look at EBOUGHEY's post from April 10, 2004 at 17:07, that is a good FPD solution to your problem.

There are many more possibilities, but you didn't give examples of how your data is structured.

 

[blumash]

Thanks TheRembler my data is simple

Abucsai Sándorn
Acél Antónia
Abonyi Pál Tamá
Ácsné Vadász Ág

Those r names , first middle & last name in one data field , I would like to seperate them to 2 or 3 differnt field depending on the name , some of the names have middle name & some r just first & last name.

Thank's

 

[TheRambler]

I borrowed some functions from the thread I posted above.
Try the following:
[tt]
CREATE dbf test (name c(30), fname c(10), mname c(10), lname c(10))
INSERT INTO test (name) VALUES ("Abucsai Sándorn")
INSERT INTO test (name) VALUES ("Acél Antónia")
INSERT INTO test (name) VALUES ("Abonyi Pál Tamá")
INSERT INTO test (name) VALUES ("Ácsné Vadász Ág")

SCAN
new_strg = TRIM(name)
* Reduce multiple spaces to a single space
DO WHILE at(' ',new_strg) > 0
new_strg = STRTRAN( new_strg,' ',' ')
ENDDO
* Count words
wcount = words(new_strg)
* Parse and replace
DO CASE
CASE wcount = 1
REPLACE fname WITH new_strg
CASE wcount = 2
REPLACE fname WITH wordnum(new_strg, 1)
REPLACE lname WITH wordnum(new_strg, 2)
CASE wcount = 3
REPLACE fname WITH wordnum(new_strg, 1)
REPLACE mname WITH wordnum(new_strg, 2)
REPLACE lname WITH wordnum(new_strg, 3)
ENDCASE
ENDSCAN

FUNCTION words
PARAMETER strg
PRIVATE strg
RETURN (OCCURS(" ", strg) + 1)

* Return word number "w_num" from string strg
FUNCTION wordnum
PARAMETERS strg, w_num
PRIVATE strg, w_num, ret_str
DO CASE
CASE w_num > 1
DO CASE
CASE AT(" ",strg,w_num-1) = 0 && No word w_num.
ret_str = ""
CASE AT(" ",strg,w_num) = 0 && w_num is last word.
ret_str = SUBSTR(strg, ;
AT(" ",strg,w_num-1)+1,255)
OTHERWISE && Word w_num is in the middle.
strt_pos = AT(" ",strg,w_num-1)
ret_str = SUBSTR(strg,strt_pos, ;
AT(" ",strg,w_num)+1 - strt_pos)
ENDCASE
CASE w_num = 1
IF AT(" ",strg) > 0 && Get first word.
ret_str = SUBSTR(strg,1,AT(" ",strg)-1)
ELSE && There is only one word.
ret_str = strg
ENDIF
ENDCASE
ret_str = ALLTRIM(ret_str)
RETURN ret_str[/tt]

Have a nice weekend.

 

[blumash]

Thank's TheRembler , U helped me a lot today , hope I will be able to thank u enough , I will try it soon.

 

[Stesvet]

Maybe it wouldbe also useful:

In the Rmbler program add 1 fiels, e.g.NAMECOMPLET yet, e.g.:

CREATE dbf test (namecomplet c(70),name c(30), fname c(10), mname c(10), lname c(10))

** and then join 3 fields simply:

REPLACE namecomplet WITH trim(name) +" // " +trim(fname)+ " // " +trim(mname) all

Stefan

 

[severach]

* So much code and so slow due to functions and looping. That's fine for a few hundred records but when you have a few million, you need to do it with a single REPLACE statement.

CLOS DATA
CREATE CURSOR TEMP ( ;
NAME C(31), ;
FNAME C(31), ;
MNAME C(31), ;
LNAME C(31))
APPEND BLANK && blank record just to be nasty, I could exclude it but I won't
APPEND BLANK
REPL NAME WITH "LAST" && we'll assume a name with no space is a last name
APPEND BLANK
REPL NAME WITH REPL("LAST",20)
APPEND BLANK
REPL NAME WITH "FIRST LAST"
APPEND BLANK
REPL NAME WITH "FIRST "+REPL("LAST",20)
APPEND BLANK
REPL NAME WITH "FIRST MIDDLE LAST"
APPEND BLANK
REPL NAME WITH " FIRST MIDDLE MIDDLE2 LAST" && yecch, extra spaces
* Ensure there's no end of string errors
SET TALK ON
* notice one set of quotes below has 2 spaces
* Step 1> Peel off the last name
* Step 2> Peel off the first name
* Step 3> The rest is the middle names
REPL ALL ;
NAME WITH ALLT(NAME), ;
LNAME WITH SUBSTR(" "+NAME,RAT(" "," "+TRIM(NAME))+1), ;
NAME WITH ALLT(LEFT(" "+NAME,RAT(" "," "+TRIM(NAME)))), ;
FNAME WITH LEFT(NAME+" ",AT(" ",NAME+" ")), ;
NAME WITH SUBSTR(NAME+" ",AT(" ",NAME+" ")+1), ;
MNAME WITH ALLT(NAME), ;
NAME WITH ""

*A few more REPL entries can do MR/MS/MRS, JR, SR, PHD, II, III, IV, V, handle punctuation, and lots more, all within a single REPLACE statement for maximum speed.