SQL Server Length Function Equivalent

Advertisements

The purpose of the Length function in SQL

The SQL LENGTH function returns the number of characters in a string. The LENGTH function is available in many Database Management Systems (DBMS).

The LENGTH Function Syntax

  • LENGTH(string)

LENGTH Function Notes

  • If the input string is empty, the LENGTH returns 0.
  • If the input string is NULL, the LENGTH returns NULL.

Length Function Across Databases

When working as a technical consultant, one has to work with customer’s databases and as you move from one database to another you will find that the function commands may vary–assuming the database has an equivalent function.

Working with VQL and SQL Server got me thing about the LENGTH() function, so, here is a quick references list, which does include the SQL Server.  

IBM DB2

  • LENGTH( )

IBM Informix

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

MariaDB

  • LENGTH( )

Microsoft SQL Server

  • LEN( )

MySQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

Netezza

  • LENGTH( )

Oracle

  • LENGTH( )

PostgreSQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

SOQL (SalesForce)

  • SOQL has no LENGTH function

VQL (Denodo)

  • LEN( )

How To Get A List Of Oracle Database Schemas?

Advertisements

Well, this is one of those circumstances, where your ability to answer this question will depend upon your user’s assigned security roles and what you actually want. 

To get a complete list, you will need to use the DBA_ administrator tables to which most of us will not have access.  In the very simple examples below, you may want to add a WHERE clause to eliminate the system schemas from the list, like ‘SYS’ and ‘SYSTEM,’ if you have access to them.

Example Administrator (DBA) Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM  DBA_OBJECTS

ORDER BY OWNER;

Example Administrator (DBA) Schema List Results Screenshot

Fortunately for the rest of us, there are All user tables, from which we can get a listing of the schemas to which we have access.

Example All Users Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM    ALL_OBJECTS

ORDER BY OWNER;

Example All Users Schema List Results Screenshot

Related References

Oracle help Center > Database> Oracle > Oracle Database > Release 19

How to Determine Your Oracle Database Name

Advertisements

Oracle provides a few ways to determine which database you are working in.  Admittedly, I usually know which database I’m working in, but recently I did an Oracle Database Express Edition (XE) install which did not goes has expected and I had reason to confirm which database I was actually in when the SQL*Plus session opened.  So, this lead me to consider how one would prove exactly which database they were connected to.  As it happens, Oracle has a few ways to quickly display which database you are connected to and here are two easy ways to find out your Oracle database name in SQL*Plus:

  • V$DATABASE
  • GLOBAL_NAME

Checking the GLOBAL_NAME table

The First method is to run a quick-select against the GLOBAL_NAME table, which. is publicly available to logged-in users of the database

Example GLOBAL_NAME Select Statement

select * from global_name;

Checking the V$DATABASE Variable

The second method is to run a quick-select a V$database. However, not everyone will have access to the V$database variable.

Example V$database Select Statement

select name from V$database;