Trim string from between specific characters

[dawgfather]

I have a set of data - essentially computer names and folders - and I need to remove the domain part of the string and leave the very beginning and the trailing part (after the .com perhaps). For example:

Source data is

server1.domain1.com:/vol/folder1

server1.domain1.com:/vol/folder2

server2.domain2.net:/vol/folder3

server3.domain.us:/folder4

What I want is to remove from the first "." up to, but not including, the ":" to get

server1:/vol/folder1

server1:/vol/folder2

server2:/vol/folder3

server3:/folder4

Any ideas?

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD SubField(Col, '.', 1) & ':' & SubField(Col, ':', 2) as NewCol,

  Col;

LOAD * Inline [

Col

server1.domain1.com:/vol/folder1

server1.domain1.com:/vol/folder2

server2.domain2.net:/vol/folder3

server3.domain.us:/folder4

];

[teiswamsler]

Hi Scott

This will Work for you

SubField( ServerName, '.', 1) & mid( ServerName, index( ServerName, ':' ) )

/Teis

[tresesco]

SubField(Data, '.',1) & ':' & Subfield(Data, ':',2)

[t_chetirbok]

Hi!

SubField(str,'.dom',1) & right(str, len(str)-index(str,':/')+1)

[#6]

Hi,

Try this.Hoping the data format could be the same throughout.

load SubField(string,'.',1)&':'&SubField(string,':',2) ;

load * inline

[

string

server1.domain1.com:/vol/folder1

server1.domain1.com:/vol/folder2

server2.domain2.net:/vol/folder3

server3.domain.us:/folder4

];