SQL ODBC query to find a last word in memo text

I am using SuiteAnalytics ODBC driver to select data from Netsuite with SQL statements.

I need to extract the last word from a memo field of the transaction table.

Many SQL function, like REVERSE, are not supported.

How can I do it?

Add Comment
2 Answer(s)

I’m concerned with how useful this site is, if a question like this can go unanswered for almost a year. You can’t do this directly in the OpenQuery as you’ve discovered since the ODBC connection only allows “SQL ’92” functionality, which severely limits it. But this is what you can do on your side, after you pull the data over.  After you’ve done that and have the data pulled over locally, you can get what you want by either using “REVERSE”, or you can do something like this below.

Make the following function:

CREATE OR ALTER function [dbo].[StringReverse](@inputstring varchar(max))
returns varchar(max)
AS
BEGIN
DECLARE @i int,
@Result varchar(max)
SET @Result=”
SET @i = 1
WHILE @i <= LEN(@inputstring)
BEGIN
SET @Result = SUBSTRING(@inputstring,@i,1) + @Result
SET @i=@i + 1
END
RETURN @Result
END

 

Then call it like this, to get the desired result:

DECLARE @string varchar(max) = ‘abc 123’;
SELECT StringReverse                                           –5. Finally, reverse the result of the substring “321” to get “123”, aka the “last word”
(
substring(                                                                       –4. Now the substring method gives you the substring of the “first” word in the reversed string
StringReverse(@string),                                         –1. Get the reverse of the input string as the source for the substring command (i.e. “321 cba”)
1,                                                                                          –2. Start at position 1 in this reversed string (i.e. starting at “3”)
PATINDEX(‘% %’, StringReverse(@string)) –3. End the substring at the matching “PATINDEX” of the first space character within the reversed string (Getting index 4 in this example)
)
);

Rookie Answered on May 24, 2023.
Add Comment

Thanks West!

Do you know other forums for Netsuite development / system analysis questions?

Rookie Answered on May 25, 2023.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor