The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

TRY_CONVERT and PARSENAME

edited October 2017 in General Discussion

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.

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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)
    
  • Wow, perfect! Thanks Jim!

  • Wow, perfect! Thanks Jim!

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

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file