CODEWITHSUNDEEP
sqldb2
Robblob
Robblob

Reputation: 77

I'm trying to remove the last two characters from my results

I am running into a problem when I pull in the city name from my database that includes the 2 character state abbreviation at the very end.

Examples

CITY
WELLSBURG WV
FRANKFORT NY
ORANGE TX

I thought I could fix the problem by using the following SUBSTR function, but the table holds 27 characters which adds spaces at the end of each string to fill the 27 character requirement. Therefore, I'm not able to use the function because the spaces at the end of each result varies.

SUBSTR(S.CITY_NAME, 1, LENGTH(S.CITY_NAME)-2) AS "CITY"

I appreciate any suggestions to get around this issue.

Upvotes: 1

Views: 10453

Answers (1)

gotqn
gotqn

Reputation: 43656

You should check the RTRIM, LTRIM and TRIM functions and use it to remove the spaces at at the end each word. Something like this:

SUBSTR(TRIM(S.CITY_NAME), 1, LENGTH(TRIM(S.CITY_NAME))-2) AS "CITY"

Check this link for more details.

Upvotes: 3

Related Questions