topic Locate first uppercase character in a string in QlikView

Locate first uppercase character in a string

Tue, 02 Dec 2014 16:58:30 GMT

Hi,

I have a field with upper and lowercase characters. I need to identify the first instance of an uppercase character in the string so that I can extract the next three characters. Is this possible? The problem I have is that the position of the first uppercase character in the string keeps on changing so I can't use LEFT/RIGHT functions. I am also unable to use the SUBFIELD function with this particular string as the data before the first uppercase character isn't consistent.

Example: 123abdJJJghk (need to extract JJJ in this string)

Re: Locate first uppercase character in a string

simenkg — Tue, 02 Dec 2014 17:03:51 GMT

You can try something like this

mid(TEXT,index(TEXT,left(Keepchar(TEXT,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),1)),3)

First it removes all other characters. Then it takes the first character and finds the first occurance of it in the original text. It then reads 3 characters.

Regards

SKG

Re: Locate first uppercase character in a string

Tue, 02 Dec 2014 18:29:25 GMT

Nicely done, Simen! That solution worked a treat.

Thanks