Replacing some text in field

[raju_salmon]

Hi,

Please help to get result for below scenario. I have field called location where some paths are available. I need to keep all paths started with C: or c: as it is and remaining drives i need to replace with 'OtherThanC'

EX: Original values - f:\link\linkng630

Result should be - OtherThanC:\link\linkng630

Location

U:\Users\Center\appdata\roaming\microsoft\windows\start menu\programs\startup

u:\users\center\appdata\roaming\microsoft\windows\start menu\programs\startup

V:

v:

v:\program files

v:\program files\dbs

f:\link\linkng630

f:\link\linkng630\lng

D:\program files (x86)

d:\program files (x86)

c:\windows\syswow64\adobe\director

C:\windows\syswow64\adobe\shockwave 12

c:\windows\syswow64\adobe\shockwave 12

c:\windows\syswow64\adobe\svg viewer

c:\windows\syswow64\adobe\svg viewer 3.0

I have tried using replace, subfield functions but cannot able to get this result. This is sample data and path could be anything irrespective of depth of path.

Can anyone help me how to create new field using 'Location' field to get accurate result?

Thanks alot.

Raju

[tresesco]

Try like:

Load

  if(upper(Left(Location,1))='C', Location, 'OtherThanC:'&SubField(Location,':',2)) as NewLocation

[rubenmarin]

Hi Raju, every row starts with a letter and a colon?, in that case;

'OtherThanC' & Mid(Location, Index(Location, ':'))

You can add a check to keep the lowercased c:

Edit: I first wrong-readed convert to 'C', if the text is OtherThanC, the check is neccesary, use the option given by tresesco.

I used mid in case there will be another colon in the string, but if this are paths colons are not allowed so there is no need to consider.

[shraddha_g]

try

if(wildmatch(left(Location,1),'c','C'),Location,Replace('Otherthanc:'&''&subfield(Location,':',-1),Location,Location))

[raju_salmon]

Thanks Tresesco,

I have two issues here.

One is my path may start more than one letter drive like

ADE:\windows\syswow64\adobe\svg viewer

Tres:\windows\syswow64\adobe\svg viewer 3.0

second one is, if i concatenate only &SubField(Location,':',2)how i can get whole path? Please help me

[its_anandrjs]

Try with this way

Data:

LOAD * Inline

[

Location

U:\Users\Center\appdata\roaming\microsoft\windows\start menu\programs\startup

u:\users\center\appdata\roaming\microsoft\windows\start menu\programs\startup

V:

v:

v:\program files

v:\program files\dbs

f:\link\linkng630

f:\link\linkng630\lng

D:\program files (x86)

d:\program files (x86)

c:\windows\syswow64\adobe\director

C:\windows\syswow64\adobe\shockwave 12

c:\windows\syswow64\adobe\shockwave 12

c:\windows\syswow64\adobe\svg viewer

c:\windows\syswow64\adobe\svg viewer 3.0

];

LOAD *,

left(Location,1),

If( Match(left(Trim(Location),1),'C') or Match(left(Trim(Location),1),'c'),Location, Replace(Location,left(Trim(Location),1),'OtherThanC')) as Status

Resident Data;

DROP Table Data;

Regards

Anand

[raju_salmon]

Unfortunately i have path without : also.

[raju_salmon]

Sorry for confusion guys... please find the attached sample data for reference.

This will be helpful to understand the data.

[tamilarasu]

Hi Raju,

Try this,

Data:

Load *,

If(Left(Location,2)<>'\\' and Upper(SubField(Location,'\',1))<>'C:',Replace(Location,SubField(Location,'\',1), 'OtherThanC:'),Location) as New_Location

From Source;

[raju_salmon]

Thanks Nagaraj,

Can you please tell me do we have any function to take out last folder/file name out of path

C:\windows\syswow64\adobe\abc.xls

c:\windows\syswow64\adobe\shockwave 12\abc.doc