help with subfield

[hopkinsc]

Hi all,

I have a field (@8) which contains user logon names, but they are a mixture of formats. i.e.

NT-DOMAIN\FirstnameLastname

NT-DOMAIN\R_FirstnameLastname

I have used the following to strip out the domain and \ so i just end up with the FirstnameLastname

lower(SubField(@8,'\',2)

this works great for the majority of the logon names but it leaves a few that are :

R_FirstnameLastname.

Can anyone tell me how to also strip out the R_ all in the same expression?

note: R_ is only one, there are other letters too with underscores (so not always R)

Thanks

[jonathandienst]

Hi

Just nest the subfields, like this:

=SubField(SubField(@8,'\',2) '_', 2)

HTH

Jonathan

[#3]

Hi

why not only

=SubField(@8, '_', 2)   which give FirstnameLastname

best regards

Chris

[hopkinsc]

Thanks Jonathan, but that fails to reload. it says its missing a close bracket, although it looks correct to me.

also, i still need to make the field lower case.

[sunilkumarqv]

try this

if you want to purge certain character then use below

purgechar ( 'NT-DOMAIN\R_FirstnameLastname','R_' ) returns ''NT-DOMAIN\FirstnameLastname'

or

Trim(Replace(Replace(Replace(SubField(@8, '\', 2), 'R_FirstnameLastname', 'FirstnameLastname'))) AS ColumnField

[hopkinsc]

Hi christian,

that would work if all of the logon names were in the format of

NT-DOMAIN\R_FirstnameLastname.

That doesnt work for the ones in the format of

NT-DOMAIN\FirstnameLastname.

[hopkinsc]

Hi Sunil,

Thanks, but as i said not all are in the format of R_, some are C_, A_ etc.

I thought i could wrap another subfield around my current one like Jonathan suggested, but i can't get it to work

[sunilkumarqv]

try this

if you want to purge certain character then use below

purgechar ( 'NT-DOMAIN\R_FirstnameLastname','R_' ) returns ''NT-DOMAIN\FirstnameLastname'

or

Trim(Replace(Replace(Replace(SubField(@8, '\', 2), 'R_F', 'F'), 'C_F', 'F'), 'A_', 'F')) AS imex

u may continue in these manner

[jerem1234 · Accepted solution]

You can try:

=mid(SubField(@8,'\',2), index(SubField(@8,'\',2), '_')+1)

Hope this helps!

[PrashantSangle]

Hi,

Try this

=SubField(SubField(@8,'\',2) ,'_', 2)