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 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.