CODEWITHSUNDEEP
sqlnetezza
Brianna Dardin
Brianna Dardin

Reputation: 33

REVERSE function in Netezza not working, how to extract file name from path without it?

My company has production and testing databases in SQL Server and a data warehouse in IBM Netezza. I wrote a query in SQL Server and now need to covert it for use in the data warehouse, however I am running into a problem.

A crucial part of the query is extracting a file name from a path, and in SQL Server I use this:

RIGHT( BitmapID, CHARINDEX( '\', REVERSE( BitmapID ) + '\' ) - 1 )

This turns "G:\grps\every\Permanent Marketing Signage\SPC\BRD\BLAD\BCAG_BLAD_001.png" to "BCAG_BLAD_001.png" and it works perfectly. I tried to convert this to Netezza syntax like so:

SUBSTRING(bit_map_ID, LENGTH(bit_map_ID) - ( STRPOS( REVERSE( bit_map_ID ), '\' ) + 2 ) )

However, when I run this, I get an error:

ERROR [42S02] ERROR: Function 'REVERSE(VARCHAR)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts

When I replace REVERSE( bit_map_ID ) with a reversed string example like "gnp.100_DALB_GACB\DALB\DRB\CPS\egangiS gnitekraM tnenamreP\yreve\sprg:G" this also works perfectly, so it's the REVERSE function that's the problem. Even though Aginity Workbench highlights the REVERSE function as if it exists, it doesn't seem to work at all - or if there is a way to make it work, I can't figure it out. I've already tried using CAST as suggested by the error message but it makes no difference.

Is there a way to reverse a string in Netezza? Or failing that, is there any other way of accomplishing what I want to do without reversing the string?

Upvotes: 0

Views: 1514

Answers (2)

Lars G Olsen
Lars G Olsen

Reputation: 1118

To my knowledge, the REVERSE function does not exist on netezza, and that is indeed what the error message above says, so I can confirm that the solution you provided is the way to go. Alternative solutions would be to use a regular expression function or a string split. To my knowledge MSsql server has none of those 3 solutions available, and the real issue for you is probably that the SQL standard does not include a list of functions needed to be compliant, so each database has its own take on which functions to include and what their interface is (negative arguments to instr in not universally accepted)

Upvotes: 0

Brianna Dardin
Brianna Dardin

Reputation: 33

I was able to figure out how to do this in Netezza without using a REVERSE function like so:

SUBSTRING( bit_map_ID, INSTR( bit_map_ID, '\', -1 ) + 1 )

The key is to use the INSTR function and specify the third argument as -1 so that it will look for the first instance starting from the end of the string instead of the beginning of the string. No reversing needed.

While this works for my needs, I would definitely be open for alternative answers for the question I posed!

Upvotes: 0

Related Questions