Cannot output all data when using left & index

[#1]

Hi,

I have a list of names of which some contain (

I am using the code below to take the values prior to the ( which works great.

left(a.name, index(a.name,'(') -1) as [Name]

However, the values without any ( aren't returned.  What's the best way to add them in?

Thanks,

Gwyn

[#2 · Accepted solution]

Hello Gwyn,

you should use an if()-statement similar to this:

Load . . .

If( index(a.name,'(')) > 0, left(a.name, index(a.name,'(') -1), a.name) AS Name,

. . .

Regards, Roland

[perumal_41]

please upload ur file

[nagaiank]

Try the following script, it works for me.

SubField(a.Name,'(',1) as Name

[#5]

Cheers Ronald, works great

[#6]

Krishnamoorthy, also works great.

For some reason, I couldn't mark yours as correct as well.  Maybe as I'd already marked Ronald's as correct. 

Sorry!

[erichshiino]

You can try to use subfield instead of your expression:

=SubField(a.name, '(', 1)

It will return everything if it doesn't find the bracket

Regards,

Erich