How to remove unwanted characters

[Anonymous]

Hi,Experts.

I want remove unwanted characters in my data.My data is below manner

sid	SNO
TT2347	1
RT76388_AW4	2
HR342344-YUE	3
TY2564 (YRU_34)	4
JKA4242(TR56)	5
UJ67383	6
YR56784	7

My output of above table is like this

sid	SNO
TT2347	1
RT76388	2
HR342344	3
TY2564	4
JKA4242	5
UJ67383	6
YR56784	7

Please give  me a solution.

Thanks in advance.

[arvind_patil]

Hi Suman,

Please use d Sub filed three time  function like below:

1. Step1

Subfield(sid,'_',1),

2. =Subfield(sid,'-',1),

3 .Subfield(sid,'(',1),

Thanks,

Arvind Patil

[arvind_patil · Accepted solution]

Hi ,

Try Below

=Subfield(Subfield(SubField(sid,'-',1),'_',1),'(',1)

Thanks,
Arvind Patil

[Anonymous]

Thanks Arvind.

Its working.You  give a good suggestion.

[sunny_talwar]

This might be another way to do it. It also gives you the option to add more characters.

Table:

LOAD *,

FindOneOf(sid&'(', '_-(') as CharacterToFind,

Left(sid, FindOneOf(sid&'(', '_-(')-1) as ReqSid;

LOAD * INLINE [

    sid, SNO

    TT2347, 1

    RT76388_AW4, 2

    HR342344-YUE, 3

    TY2564 (YRU_34), 4

    JKA4242(TR56), 5

    UJ67383, 6

    YR56784, 7

];

[Anonymous]

Thanks sunny.

You give me good suggestion.