1

The following query produces the examples below. I want to pull only what is after the last (always the 3rd) semi colon. Each output will have different cities so I can't just trim it a certain amount of spaces as it will vary. 

SELECT  MAX(E.EVENT_DESC) 
FROM    IASDB.EVENT E 
WHERE   SL.INVOICE_NO = E.INVOICE_NO 
AND     E.EVENT_CODE IN 'EDL'

Examples:

Consignee;Jeffersonville,IN;J 6

Consignee;Nashville,TN;J 14

What do I need to do to only pull the characters that come after the last semi colon? (ex: 'J 6' or 'J 14')

Chicken_Hawk
  • 71
  • 1
  • 10
  • 1
    Which DBMS are you using? Postgres? Oracle? Btw: the query shown will **not** generate the output shown. The query will only return a single row, never two rows. –  Jan 08 '16 at 19:24
  • this post shows a split type function on a character using CHARINDEX http://stackoverflow.com/questions/9260044/how-to-split-a-string-after-specific-character-in-sql-server-and-update-this-val – Dr. Aaron Dishno Jan 08 '16 at 19:25
  • 3
    DbVisualizer is a SQL client **not** a DBMS. It's hard to believe that you don't know which DBMS you are connecting to using that tool –  Jan 08 '16 at 19:27
  • although i am zero in DB2 but here is link which definitely solves your purpose http://stackoverflow.com/questions/3227798/database-substring-upto-first-occurence-of-character you need to use right function in your case because you want to get text only after last semicolon – DevelopmentIsMyPassion Jan 08 '16 at 19:44
  • Which DB2? locate_in_string might help you here if you have a new enough version. – Greg Viers Jan 08 '16 at 19:52
  • Your query is not syntactically correct. You should be getting an error on `SL.INVOICE_NO`. – Gordon Linoff Jan 08 '16 at 20:36

1 Answers1

1

This should work in DB2:

SELECT  MAX(RIGHT(
         E.EVENT_DESC
        ,LENGTH(E.EVENT_DESC) - LOCATE_IN_STRING(E.EVENT_DESC,';',-1)))
FROM    IASDB.EVENT E 
WHERE   SL.INVOICE_NO = E.INVOICE_NO 
AND     E.EVENT_CODE IN 'EDL'
Greg Viers
  • 3,473
  • 3
  • 18
  • 36