Skip to Content
avatar image
Former Member

Can a string column be flipped inside a case statement?

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 ?

Add comment
10|10000 characters needed characters exceeded

  • 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

  • Get RSS Feed

1 Answer

  • Nov 14, 2017 at 06:58 PM

    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
    Add comment
    10|10000 characters needed characters exceeded