split strings when capital and small letters are used

[jordan231]

Hej , i struggle to get correct names from a string. 

James SmithMary JohnsonJohn WilliamsPatricia BrownRobert Jones-GarciaMichael MillerLinda Davis

I was thinking about finding one of aA aB...zZ letters because first name of new record starts with capital always and small letters is at the end of previous record. 

Any idea how to loop over all records like this ? 

[marcus_sommer · Accepted solution]

[marcus_sommer]

... the origin post was permitted because of KML ...

[-SW- · Accepted solution]

try the answer from this post:

https://community.qlik.com/t5/Visualization-and-Usability/Parse-string-based-on-Capitalize/m-p/15640...

[rwunderlich]

If you are on Qlik Cloud or May 2025 release you can use the ExtractRegEx() function like this:

Names:
LOAD
ExtractRegEx(Names, '[A-Z][a-z]+(?:\s[A-Z][a-z]+(?:-[A-Z][a-z]+)?)?') as Name
Inline [
Names
James SmithMary JohnsonJohn WilliamsPatricia BrownRobert Jones-GarciaMichael MillerLinda Davis
]
;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[jordan231]

Thanks for all updates and inspiration. I used mixed variants , since i m not in cloud i did a list of names using  script below.  now i transfor names with subfield. Many thanks !

SmallLetters:

LOAD Chr(97 + IterNo() - 1) as SmallLetter

AUTOGENERATE 1

WHILE IterNo() <= 26;

// Capital

left join

LOAD Chr(65 + IterNo() - 1) as CapitalLetter

AUTOGENERATE 1

WHILE IterNo() <= 26;

Combinations:

MAPPING LOAD 

    SmallLetter & CapitalLetter as Combination,

    SmallLetter & ' ' & CapitalLetter as CombinationWithSpace

RESIDENT SmallLetters;

DROP TABLES SmallLetters;

//

//

LOAD NameSurname,

Trim(MapSubString('Combinations', NameSurname)) as Formated;

LOAD * INLINE [

    NameSurname

    John DowTom CruiseJack Daniels

    JaneDow

];

//