Printing a single field on multiple lines

[xsw1971]

I have a field that contains an IP address. Occassionally, there will be two IP addresses for one field. I separate them with a comma and no space (eg, xxx.xxx.xxx.xxx,yyy.yyy.yyy.yyy) I would like to print and sort a list of all IP addresses. Is there a command to use that will recognize the comma (or any other specified character) in a field as a separator, so that it prints the characters before the comma on one line, and the characters after the comma on the next line?

 

[MalcolmW]

What database are you using? Do you want an ANSI SQL solution, or do you primarily just want something that will work on your specific database? Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[xsw1971]

It's an Oracle database and we are using ColdFusion as a front end, however I am just creating some simple reports directly in SQL*Plus. I was told by someone that I could break apart a field using a designated charater, but I don't know if that happens in SQL*Plus or ColdFusion. So, if there is an ANSI SQL solution, I suppose that's what I want.
Thank you,
Jennie M

 

[karluk]

"Instr" is a function that will return the position of the comma within your character string. You can combine it with the substring function, "substr", to select the ip addresses before and after the comma.

Suppose that the column name is "ip_col". Then you could list all ip addresses within the column with

select ip_col from your_table
where ip_col is not like '%,%'
union
select substr(ip_col,instr(ip_col,',')+1) from your_table
where ip_col is like '%,%'

 

[karluk]

Oops, my sql statement omits the first ip address in a pair. Hopefully the following is correct.

select substr(ip_col,instr(ip_col,',')+1) from your_table
union
select substr(ip_col,1,instr(ip_col,',')-1) from your_table
where ip_col is like '%,%'

 

[xsw1971]

Well, it sort of works. The code I tried is:

select substr(ip_addr,instr(ip_addr,',')+1), identifier
from resources
union
select substr(ip_addr,1,instr(ip_addr,',')-1), identifier
from resources
where ip_addr is like '%,%';

and I get an ORA-00908: missing NULL keyword error. If I add a not null condition to either or both queries, I get the same message. If I remove the '%,%' line, I get the right results but the number of rows are doubled, because it's creating two rows for every record, regardless of whethere there is 1 or 2 IP addresses.

Any thoughts?
Thanks!
Jennie M

 

[karluk]

Pardon the sloppy syntax. You need to remove the "is" from the sql.

select substr(ip_addr,instr(ip_addr,',')+1), identifier
from resources
union
select substr(ip_addr,1,instr(ip_addr,',')-1), identifier
from resources
where ip_addr like '%,%';

 

[xsw1971]

Thank you, it works great!
Jennie M