LEFT - Remove everything before the .

[stwp86]

Anyone know how this snippet would translate in to Vertica SQL . . . seems to not like CHARINDEX.

Code: Select all

LEFT(Column1, CHARINDEX('.', Column1) - 1)

Thanks!

[JimKnicely]

Hi,

In MS SQL Server:



In Vertica, you can try the INSTR function or the POSITION function:

Code: Select all

dbadmin=> SELECT INSTR('Vertica. is cool', '.'),
dbadmin->        POSITION('.' IN 'Vertica. is cool');
 INSTR | position
-------+----------
     8 |        8
(1 row)

So including the LEFT function, you could do one of the following:

Code: Select all

dbadmin=> SELECT LEFT('Vertica. is cool', INSTR('Vertica. is cool', '.') - 1),
dbadmin->        LEFT('Vertica. is cool', POSITION('.' IN 'Vertica. is cool') - 1);
  LEFT   |  LEFT
---------+---------
 Vertica | Vertica
(1 row)

I hope this helps!

[stwp86]

Thank you!

I also came across this method . . .

Code: Select all

split_part(column1, '.', 1)