Impromptu - text to columns

[jatmon]

Hi,

Does anyone know of a way to split a text column into separate columns in Impromptu. For example, in our DB we hold Forename(s) and Surname. The Forename(s) column needs to be split for anyone with more than one forename.

i.e :

Before split:

Fornames | Surname
-------------------|-----------
Micheal Andrew | Fox


After split :

Forname1 | Forename2 | Surname
-------------|----------------|-----------
Micheal | Andrew | Fox

 

[drlex]

A solution will depend on what functions your database supports and the number of possible forenames (I know of one chap with 11 forenames!).

If you have only two and the DB supports locate, left and right and length, you could use the following

forename1 = LEFT(T1."forename",LOCATE(' ',T1."forename",1))
forename2 = RIGHT(T1."forename",(LENGTH(T1."forename") - LOCATE(' ',T1."forename",1))

If there are more, then MID or SUBSTRING would be required to parse out the middle forenames.

soi la, soi carré

 

[drlex]

My error - if there is only one forename, it will end up in forename2.
Correction:

forename = IF((locate(' ',T1."forename",1)) = 0) THEN (T1."forename") ELSE (left(T1."forename",(locate(' ',T1."forename",1))))
forename2 = IF ((locate(' ',T1."forename",1)) <> 0) THEN (right(T1."forename",(length(T1."forename") - (locate(' ',T1."forename",1)))) ELSE ('')

Of course, some DBs support first-word and last-word, and therefore save all this mucking about

soi la, soi carré

 

[jatmon]

Thanks for that, I'll give it a try.

Our DB does support First-word, but for some reason, Last word does not seem to be supported.

 

[drlex]

You can use REVERSE in conjunction with FIRST-WORD, but an entry with one forename would be duplicated in forename1 and forename2 unless you check for equivalence
eg.

forename1 = first-word(forename)
forename2 = if reverse(first-word(reverse(forename))) = first-word(forename) then '' else reverse(first-word(reverse(forename)))

soi la, soi carré