remove id's from full name column

[ritumishra01]

while pulling data from source i am even getting id's like pcd090 , pcm090, rxm987, wvx098, and many more i want to remove these ids and only get the first and last name in my full name column.

how can i achieve that

[justISO]

Hi, let assume in source you have name in format 'XXXXXX Name Surname', where these XXXXXX are your 6 symbol ids. To get just 'Name Surname' you can take rest of your source name by ignoring first 6 symbols, something like this:

=trim(mid(name, 7, len(name)))

[ritumishra01]

Actually its like 

rxm800

pop000

xmf567

bgt567

mji

Abhay mishra

zoya khan

andrew rom

jeeffrey hebat

so i want to remove all the ids from the list how can i do that 

[MayilVahanan]

HI

if Id has atleast one number, then try like below

If(Keepchar(field, 0123456789), '', field) as FullName

[justISO]

in this case (and if we think, that 'mji' is not 'correct' full name), I would take only rows where is 'space' symbol somewhere in between (separating name and surname), like this:

WHERE index(trim(name), ' ')>0