The CNXNAME function is used to parse string_exp data fields so that they may be reconstituted into a more desired format.

The string_exp parameter is the field that contains the data.

The nameformat_exp parameter is a string value that is derived from the format of your data. Each part of the name is assigned a character (Last = 'L', First = 'F', Middle = 'M', Suffix (Jr., Sr., III, etc.) = 'S'). If there is a comma separating portions of the name, that is denoted with a 'C'. It is assumed that there will always be a space between portions of the name.

The nameoutput_exp parameter is a string value created from the same characters as the nameformat, in addition to a space (' ') character.

 

Example:

If the data field is in the format "Last<comma><space>First<space>Middle", the nameformat parameter would be 'LCFM'. If you wanted the output to be in the format "First<space>Last", the nameoutput parameter would be 'F L'. Using these values, the final function would resemble the following:

 

SELECT CNXName(NAME, 'LCFM', 'F L') from MyTable