Replacing characters during load

[dawgfather]

I have some data fields that have some additional (unwanted) characters that I need to remove. I am looking to do this during the load process in order to make my data field uniform.

What is a simple process to replace (or remove) a set of characters like "u1:/vol" or "u2:/vol", etc.? It seems that a "replace" with something like "u*:/vol" with ":/vol" to strip out the "u*" piece would work but I know not how to phrase or code that.

Sample data:

server1:/vol/A

server1u1:/vol/B

server2u6:/vol/C

server3u3:/vol/C

To get:

server1:/vol/A

server1:/vol/B

server2:/vol/C

server3:/vol/C

[dawgfather]

I do need to be sure that maybe it's scaled down to more of replacing the "u" and the single character after it. I realize that a wildcard like "u*:/vol" covers things like "u2:/vol" (which would be in scope) and/or "uat4321:/vol" (which falls out of scope). I am only looking to pull out the trailing "u?" off a server name that appears next to ":/vol".

Sample data:

server1:/vol/A

server1u1:/vol/B

server2u6:/vol/C

server3u3:/vol/C

superserver12:/vol/D     (has a "u" in the name but it's not at the end of the server name)

To get:

server1:/vol/A

server1:/vol/B

server2:/vol/C

server3:/vol/C

superserver12:/vol/D

[sunny_talwar]

May be this:

Table:

LOAD *,

  SubField(Name, 'server', 1) & 'server' & SubField(TextBetween(Name, 'server', ':/vol'), 'u', 1) & ':/vol' & SubField(Name, ':/vol', 2) as NewName;

LOAD * Inline [

Name

server1:/vol/A

server1u1:/vol/B

server2u6:/vol/C

server3u3:/vol/C

superserver12:/vol/D

];

[dawgfather]

Fair enough and looks like it works in the simple sample but my server names are not all "server" so perhaps a better (more accurate) sample data set would be (and complicate the process):

Original:

JAX123ABC1:/vol/A               Stays the same

JAX123ABC1u1:/vol/B              The trailing "u1" removed

JAX567DEF2:/vol/A               Stays the same

ATL24D65BU2n1:/vol/C          Stays the same even though there is a "U" in the name - it's not a trailing "u#"

ATL3579XYZ:/vol/D               Stays the same

HOU12345A7:/vol/E               Stays the same even though there's a "U" in the name - it's not a trailing "u#"

HOU12345678A7u4:/vol/E           Needs to change - the trailing "u1" removed

Wanted:

JAX123ABC1:/vol/A

JAX123ABC1:/vol/B

JAX567DEF2:/vol/A

ATL24D65BU2n1:/vol/C

ATL3579XYZ:/vol/D

HOU12345A7:/vol/E

HOU12345678A7:/vol/E

[sunny_talwar · Accepted solution]

What about this:

Table:

LOAD *,

  If(Left(Right(SubField(Name, ':/', 1), 2), 1) = 'u', Left(SubField(Name, ':/', 1), (Len(SubField(Name, ':/', 1)) - 2)), SubField(Name, ':/', 1)) &

  ':/' & SubField(Name, ':/', 2) as NewName;

LOAD * Inline [

Name

JAX123ABC1:/vol/A

JAX123ABC1u1:/vol/B

JAX567DEF2:/vol/A

ATL24D65BU2n1:/vol/C

ATL3579XYZ:/vol/D

HOU12345A7:/vol/E

HOU12345678A7u4:/vol/E 

];

Only time this won't work correctly is if there is a Name like this:

JAX123ABu1:/vol/A where second last character before 😕 is a u. If this won't happen and anytime 2 letter before 😕 is u needs to be removed, the above should work.