2 typo
source | link

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator

? Conditional

One way to implement the Derived Column would be to parse the string like this

 TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1
 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) )

In this case, "name" is the column that holds the string to be parsed.

What we are doing here is:

  1. Remove any trailing white-space with RTRIM( name )
  2. Reverse the string so we are dealing with thenthe end as the beginning with REVERSE()
  3. Grab the first "token" delimited by a backslash using TOKEN()
  4. Count how many "." delimiters are in the result using TOKENCOUNT()
  5. Test the count of "." delimited tokens is equal to 1 using ?
  6. If the count is 1, return an empty string - no extension.
  7. Otherwise return the first token before the "." using TOKEN()
  8. Reverse the extension to get back to "normal" using REVERSE()

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator

? Conditional

One way to implement the Derived Column would be to parse the string like this

 TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1
 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) )

In this case, "name" is the column that holds the string to be parsed.

What we are doing here is:

  1. Remove any trailing white-space with RTRIM( name )
  2. Reverse the string so we are dealing with then end as the beginning with REVERSE()
  3. Grab the first "token" delimited by a backslash using TOKEN()
  4. Count how many "." delimiters are in the result using TOKENCOUNT()
  5. Test the count of "." delimited tokens is equal to 1 using ?
  6. If the count is 1, return an empty string - no extension.
  7. Otherwise return the first token before the "." using TOKEN()
  8. Reverse the extension to get back to "normal" using REVERSE()

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator

? Conditional

One way to implement the Derived Column would be to parse the string like this

 TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1
 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) )

In this case, "name" is the column that holds the string to be parsed.

What we are doing here is:

  1. Remove any trailing white-space with RTRIM( name )
  2. Reverse the string so we are dealing with the end as the beginning with REVERSE()
  3. Grab the first "token" delimited by a backslash using TOKEN()
  4. Count how many "." delimiters are in the result using TOKENCOUNT()
  5. Test the count of "." delimited tokens is equal to 1 using ?
  6. If the count is 1, return an empty string - no extension.
  7. Otherwise return the first token before the "." using TOKEN()
  8. Reverse the extension to get back to "normal" using REVERSE()
1
source | link

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator

? Conditional

One way to implement the Derived Column would be to parse the string like this

 TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1
 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) )

In this case, "name" is the column that holds the string to be parsed.

What we are doing here is:

  1. Remove any trailing white-space with RTRIM( name )
  2. Reverse the string so we are dealing with then end as the beginning with REVERSE()
  3. Grab the first "token" delimited by a backslash using TOKEN()
  4. Count how many "." delimiters are in the result using TOKENCOUNT()
  5. Test the count of "." delimited tokens is equal to 1 using ?
  6. If the count is 1, return an empty string - no extension.
  7. Otherwise return the first token before the "." using TOKEN()
  8. Reverse the extension to get back to "normal" using REVERSE()