NZSQL/Code - LEFT() Function in Netezza NZ-SQL

sql netezza
cryocaustik picture cryocaustik · Dec 22, 2014 · Viewed 18.3k times · Source

Everyone!

I am trying to find how to use, or a replacement for the LEFT() function for Netezza/NZSQL.

What I need is to grab the first two charectors of a string. The code I used to use in TSQL is as follows:

LEFT(COLUMN_A,2) AS Column_Name

is there a similar function in NZSQL? When I type in "LEFT" it turns grey, but is not recognized as a function and acts like it is misisng another part...

Thanks in advance!

Answer

ScottMcG picture ScottMcG · Dec 22, 2014

You can use the built in function SUBSTR() or you can use the STRLEFT() function that is included with the SQL Extension Toolkit. The latter option will only be available if it has been installed.

TESTDB.ADMIN(ADMIN)=> select SUBSTR('ABCDED',1,2);
 SUBSTR
--------
 AB
(1 row)

or

TESTDB.ADMIN(ADMIN)=> select STRLEFT('ABCDED',2);
 STRLEFT
---------
 AB
(1 row)

Related questions

How to delete duplicate rows without unique identifier

I have duplicate rows in my table and I want to delete duplicates in the most efficient way since the table is big. After some research, I have come up with this query: WITH TempEmp AS ( SELECT name, ROW_NUMBER() …

Read More
Netezza SQL syntax to convert numeric YYYYMMDD format into date

We have a Netezza table that contains dates stored in a numeric YYYYMMDD format (eg 20090731). What is the best Netezza syntax to use to convert this into date format? eg SELECT somefunction(20090731) as NZDATE ?

Read More
How do I list all the column names in Netezza?

Is there a query I can write to search all the column names for a particular database in Netezza?

Read More