TRY_CONVERT and PARSENAME

Slaagh

Hello,

I do not have access to Vertica.com. My team is looking into this. So I have to use the forums.

Does anyone know the equivalent to TRY_CONVERT and PARSENAME in Vertica? They are SQL Server functions. Below is what I need to convert to Vertica.

select CONVERT(BIGINT, PARSENAME(IP_Address, 4))256256256 + CONVERT(BIGINT, PARSENAME(IP_Address,3))256256 +
CONVERT(BIGINT, PARSENAME(IP_Address,2))256 + CONVERT(BIGINT, PARSENAME(IP_Address,1))
from IP

Thank you all in advance.

Jim_Knicely

For TRY_CONVERT, the "::!" construct should help:

dbadmin=> select * from test;
 c
---
 A
 1
(2 rows)

dbadmin=> select c, c::int from test;
ERROR 2827:  Could not convert "A" from column test.c to an int8

dbadmin=> select c, c::!int from test;
 c | c
---+---
 A |
 1 | 1
(2 rows)

For PARSENAME, maybe user SPLIT_PART?

dbadmin=> select split_part('192.168.1.1', '.', 1);
 split_partb
-------------
 192
(1 row)

DBDave

Wow, perfect! Thanks Jim!

DBDave

Wow, perfect! Thanks Jim!

DBDave

Jim - you just saved me a huge headache. Thanks again.

DBDave

Jim - you just saved me a huge headache. Thanks again.