need to trim a string - substr function?

Database

need to trim a string - substr function?

Hi,

I apologize if this is a very elementary question.

I need to pull a few values from Data Warehouse. One of the values needs to be formatted, where the nine characters that are two characters from the end of the string needs to be isolated. For example, if the string is ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789, I would need to isolate: YZ1234567.

My query is as follows:

Select
count(Distinct record_id) As DistinctRecCount,
ITEM_NAME As Name,
substr(LONG_STRING,24,9) as SHORT_STRING

From NAME_OF_TABLE

Where cast (RECORD_DATE As date) Between Cast ('2008-08-01' As date)
And cast ('2008-08-31' As date)

Group By SHORT_STRING

Any help would be appreciated. Thank you.
-Brian.
4 REPLIES
Enthusiast

Re: need to trim a string - substr function?

Something like this (be aware I have not tested the solution)?

Good luck.

Select
count(Distinct record_id) As DistinctRecCount,
ITEM_NAME As Name,
left(substr(trim(LONG_STRING),characters(trim(LONG_STRING)) - 11, 11), 9) as SHORT_STRING
From NAME_OF_TABLE
Where cast (RECORD_DATE As date) Between Cast ('2008-08-01' As date)
And cast ('2008-08-31' As date)
Group By SHORT_STRING
Enthusiast

Re: need to trim a string - substr function?

The above mentioned logic is correct but with a small correction
i.e
instead of Left(Substr(Trim(long_string),characters(Trim(long_string)) - 11, 11), 9) As SHORT_STRING
use Left(Substr(Trim(long_string),characters(Trim(long_string)) - 10, 11), 9) As SHORT_STRING.

The change is 10 shuld be subtracted instead of 11.

Also the following works
Substr(Trim(long_string),characters(Trim(long_string)) - 10, 9) As SHORT_STRING

Cheers:-)

Re: need to trim a string - substr function?

Hi,

I need to trim the last two characters in a string.like if the string is 123456AB,then I require the result to be 123456.

Can you please help me in this.

Junior Contributor

Re: need to trim a string - substr function?

This should work:

case when char_length(x) > 2 then substring(x from 1 for char_length(x) - 2) else '' end

Dieter