logo
down
shadow

What is the equivalent of T-SQL ISNUMERIC function in HANA Sqlscript?


What is the equivalent of T-SQL ISNUMERIC function in HANA Sqlscript?

By : Fred
Date : November 19 2020, 01:01 AM
seems to work fine SAP HANA does not come with a ISNUMERIC() function. However, this question had been asked and answered multiple times on SCN: E.g. http://scn.sap.com/thread/3449615
or my approach from back in the days: http://scn.sap.com/thread/3638673
code :
drop function isnumeric;
create function isNumeric( IN checkString NVARCHAR(64))
returns isNumeric integer
language SQLSCRIPT as
begin
declare tmp_string nvarchar(64) := :checkString;
declare empty_string nvarchar(1) :='';

/* replace all numbers with the empty string */
tmp_string := replace (:tmp_string, '1', :empty_string);
tmp_string := replace (:tmp_string, '2', :empty_string);
tmp_string := replace (:tmp_string, '3', :empty_string);
tmp_string := replace (:tmp_string, '4', :empty_string);
tmp_string := replace (:tmp_string, '5', :empty_string);
tmp_string := replace (:tmp_string, '6', :empty_string);
tmp_string := replace (:tmp_string, '7', :empty_string);
tmp_string := replace (:tmp_string, '8', :empty_string);
tmp_string := replace (:tmp_string, '9', :empty_string);
tmp_string := replace (:tmp_string, '0', :empty_string);

/*if the remaining string is not empty, it must contain non-number characters */
if length(:tmp_string)>0 then
    isNumeric := 0;
else  
    isNumeric := 1;
end if;

end;
select val, isNumeric(val)  from data 

VAL         ISNUMERIC(VAL)
1blablupp   0            
1234        1            
bla123      0   


Share : facebook icon twitter icon
SAP HANA Studio: Use SQLScript without writing a procedure

SAP HANA Studio: Use SQLScript without writing a procedure


By : Ajith S Kumar
Date : March 29 2020, 07:55 AM
wish helps you Since HANA SP10 there is the possibility to use anonymous blocks:
DO BEGIN -- your logic END
Add a column to SQLScript result on HANA

Add a column to SQLScript result on HANA


By : Pablo Ogando Ferreir
Date : March 29 2020, 07:55 AM
I hope this helps . The problem with your query is the you used double quotes instead of single quotes for the content of the source column.
Please change your query to:
code :
(SELECT *, 'A' AS SOURCE FROM A) UNION ALL (SELECT *, 'B' AS SOURCE FROM B)
Recursive self-join in HANA SQLSCRIPT procedure

Recursive self-join in HANA SQLSCRIPT procedure


By : Chris Hubbuch
Date : March 29 2020, 07:55 AM
wish helps you Sorry, but you mistook the academic test implementation of a query processing feature for a part of delivered standard product functionality.
SAP HANA does not support recursive queries as described in the paper.
SAP HANA: convert abap date to hana date with sqlscript keeping null value on empty date

SAP HANA: convert abap date to hana date with sqlscript keeping null value on empty date


By : Darren Fatboyslim
Date : March 29 2020, 07:55 AM
wish helps you DATS_TO_DATE does not return NULL if the given date is initial (0000-00-00), but a special date value (-1-12-31 to be precise).
To receive a NULL value in this case, as you requested, use the following statement:
Dynamic SQL Syntax Error SQLScript SAP HANA

Dynamic SQL Syntax Error SQLScript SAP HANA


By : sampath chandana
Date : March 29 2020, 07:55 AM
hope this fix your issue You should check the generated SQL string by selecting it for output, like so:
code :
BEGIN

        select                'select distinct "'|| IDColumnName ||'" from 
                        "Schema"."ZT.'|| DimPartialName ||'" dim
                        LEFT JOIN "Schema"."SourceTable" raw on 
                        dim."AUDIT.CREATED_ON" = raw."Audit.CREATED_ON"
                        where "UPLOAD_UUID" = ' || UploadID ||' ' as SQLTEXT from dummy;

End;
select distinct "Y" from 
                    "Schema"."ZT.X" dim
                    LEFT JOIN "Schema"."SourceTable" raw on 
                    dim."AUDIT.CREATED_ON" = raw."Audit.CREATED_ON"
                    where "UPLOAD_UUID" = Z 
BEGIN

    execute immediate 
                  'select distinct "'|| :IDColumnName ||'" ' 
               || 'from '
               || '"Schema"."ZT.'|| :DimPartialName ||'" dim '
               || 'LEFT JOIN "Schema"."SourceTable" raw on '
               || 'dim."AUDIT.CREATED_ON" = raw."Audit.CREATED_ON" '
               || 'where "UPLOAD_UUID" = ''' || :UploadID ||''' ';

END;
Related Posts Related Posts :
  • How to create a database snapshot in SQL Server 2008 R2
  • Query speed and expressions with constant value
  • How to do a complex calculation as this sample
  • Passing a ResultSet into a Postgresql Function
  • Netsuite custom formula field using a CASE statement with multiple WHEN conditions returning "invalid expression&qu
  • Execute query command (dynamic sql) runs faster than ordinary query
  • How to extract only numbered rows from a column
  • INSTEAD OF DELETE trigger (Postgresql)
  • Oracle Apex, when a report with sql is created, how do i get it return after entering in to a text box?
  • Select records based on two tables where one column in one table starts with the records from another tables column usin
  • How to write select using two ids combination in Oracle 11g
  • Frequently use of 'UPDATE STATISTICS WITH FULLSCAN' is normal?
  • need query for this simple pulling data sql server
  • Sql query by using decode to show grades of employee
  • Not a single function error in Oracle
  • Oracle SQL optimizer's behavior when dealing with ORs and row-independent predicates (such as functions returning same v
  • Combining output of two or more select statement
  • Must Declare Scalar Variable Exception
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org