Character Truncation

Database
Enthusiast

Character Truncation

Hi All,

I have the values like below,

 col1

=======

66.249.71.204

186.104.145.143

190.110.152.52

200.83.137.118

201.241.3.149

190.20.3.0

186.78.90.0

190.82.5.0

i want the data to be displayed like below, remove anything after last "."

 Output

66.249.71

186.104.145

190.110.152

200.83.137

201.241.3

190.20.3

186.78.90

190.82.5

I have tried with substring and position but not getting the correct output for all the records.

Any help?

Thanks

Senthil

4 REPLIES
mjj
Teradata Employee

Re: Character Truncation

Hi,

Try this:

sel case when substr(t.ip, char_length(t.ip)) = '.' then substr(t.ip, 1, char_length(t.ip) -1) else t.ip

end

from(

sel case char_length(col1)

when 15 then substr(col1,1,char_length(col1) - 4)

when 14 then substr(col1,1,char_length(col1) - 3)

when 13 then substr(col1,1,char_length(col1) - 4)

when 13 then substr(col1,1,char_length(col1) - 3)

when 11 then substr(col1,1,char_length(col1) - 2)

when 10 then substr(col1,1,char_length(col1) - 2)

end ip

from t1

)t

Rgds,

Senior Apprentice

Re: Character Truncation

Use the INSTR Oracle function, which allows searching backwards and the Nth occurance (but better ask some Teradata PS/CS guys for the latest version of those Oracle UDFs instead of downloading it)

substring(col1 from 1 for INSTR(col1,'.',-1,1) -1)

Dieter

Re: Character Truncation

Hi, try this:

SELECT t.outputstr FROM

(

   SELECT

      POSITION('.' IN ip) AS POS1

      ,POSITION('.' IN SUBSTRING(ip,POS1+1,50)) + POS1 AS POS2

      ,POSITION('.' IN SUBSTRING(ip,POS2+1,50)) + POS2 AS POS3

      ,SUBSTR(ip, 1, POS3 - 1) AS outputstr

   FROM t1

)t

Enthusiast

Re: Character Truncation

Thanks all for the suggestions, below code seems to be fitting my requirement(may not be ideal solution :-)).

substr(ip_address, 0,8+(position('.' in substr( ip_address,9)) )) as ip_address_trunc 

Thanks again!