Menu
I'm trying to split a given IPv4 address into four numbers.
In SQL Server this query works well for me:
select CAST (PARSENAME('10.20.30.40',4) AS INT)
result: 10
select CAST (PARSENAME('10.20.30.40',3) AS INT)
result: 20
and so on.
I need the equivalent syntax in Oracle SQL, but can't find it. Any idea?
709
The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and ' ' as the second argument.
I used STRING_SPLIT() which is a table valued function supports SQL server 2016 and higher versions. You need to provide the formatted string into this function and use cross apply to join and generate the desired output. Save this answer.
How do I split a string in Oracle SQL Developer? Using replace ( str, ',' ) to remove all the commas from the string. Subtracting the length of the replaced string from the original to get the number of commas. Add one to this result to get the number of values.
PL/SQL Function to Split a StringSource_String: String to Split. Field_Position: Delimiter Position. UnTerminated: False by default, but if you pass True, then it will add the specified delimiter at the end of the string. Delimiter: Default is Comma ',', you can pass any other delimiter.
You could use regexp_substr:
select ip,
regexp_substr(ip, '\d+',1,1) as first_octet,
regexp_substr(ip, '\d+',1,2) as second_octet,
regexp_substr(ip, '\d+',1,3) as third_octet,
regexp_substr(ip, '\d+',1,4) as fourth_octet
from (select '10.20.30.40' AS ip from dual )ips;
Rextester Demo
79
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
