USING SELECT STATEMENT TO GET FORMATTED IP ADDRESS

[johngiggs]

I am attempting to use a select statement to gather information about backups. I am casting the address as a 14 character field, however when an IP address has less than 14 characters it will begin to display the first few numbers of the port number. Ex. 155.44.33.1 will display as 155.44.33.1:12 because it is displaying the first 14 characters. How can I exclude everything from the colon on?
Below is the select statement that I'm using.

select cast(entity as char(15)) "NODE NAME", cast(address as char(14)) "ADDRESS", cast(schedule_name as char(15)) "SCHEDULE NAME", cast(successful as char(10)) SUCCESSFUL, cast(start_time as char(16)) "START TIME", cast(end_time as char(16)) "END TIME" from summary where activity='BACKUP' and start_time>'2003-01-13 12:00:00'
and successful='NO'

Any help would be greatly appreciated.

Thanks,

John

 

[ricciocri]

This should work:

Substitute in your select:

cast(address as char(14)) "ADDRESS"

With :

substr(address,0,posstr(address,':')) as "ADDRESS"


Riccardo.

Try an open source Tsm report Tool:

http://mytsmreport.sourceforge.net/