Can a string column be flipped inside a case statement?

[Former Member]

I have a string column that if it hits a certain qualification I want to flip the string column around. Is this possible to do? Below is my current case statment. If it hits the statment in bold, then I want to flip the string around.

CASE WHEN COMN_PRSNTN.EDW_SIGNET_LOCATION_TBL.SOURCE = 'A' THEN COMN_PRSNTN.EDW_SIGNET_LOCATION_TBL.REGION_LAST_FIRST_NAME

11-14-2017-10-13-23-am.jpg

If the source is not ‘A’ and it uses the area_last_first_name (in bold above) I want to take this name then and flip it. It is a ‘string’ column.

So I would want it to display DEBORAH, HUGHES instead or DEBORAH HUGHES is that something that is possible to do ?

[Former Member]

not sure why but the whole statment did not post

CASE WHEN COMN_PRSNTN.EDW_SIGNET_LOCATION_TBL.SOURCE = 'A' THEN COMN_PRSNTN.EDW_SIGNET_LOCATION_TBL.REGION_LAST_FIRST_NAME

ELSE COMN_PRSNTN.EDW_SIGNET_LOCATION_TBL.AREA_LAST_FIRST_NAME

END

[Joe_Peters]

So if SOURCE is not 'A', you want to reformat "last, first m" into "first m last"?

If so, and you're using Oracle, it's a simple regexp_replace:

CASE WHEN EDW_SIGNET_LOCATION_TBL.SOURCE = 'A' 
     THEN EDW_SIGNET_LOCATION_TBL.REGION_LAST_FIRST_NAME
     ELSE regexp_replace(EDW_SIGNET_LOCATION_TBL.AREA_LAST_FIRST_NAME,'(.*), (.*)','\2 \1')
     END

If you're using a db without regexp_replace, you can do it the long way:

CASE WHEN EDW_SIGNET_LOCATION_TBL.SOURCE = 'A' 
     THEN EDW_SIGNET_LOCATION_TBL.REGION_LAST_FIRST_NAME
     ELSE substr(EDW_SIGNET_LOCATION_TBL.AREA_LAST_FIRST_NAME,
              instr(EDW_SIGNET_LOCATION_TBL.AREA_LAST_FIRST_NAME,',')+2,
              999
           ) ||
          substr(EDW_SIGNET_LOCATION_TBL.AREA_LAST_FIRST_NAME,
              1,
              instr(EDW_SIGNET_LOCATION_TBL.AREA_LAST_FIRST_NAME,',')-1
          )
END