Skip to main content

Count number of digits and decimal points in a table column 

WITH S AS (/*SELECT QUERY*/
  )
SELECT
  /*COLUMN NAMES*/
  ,
  LENGTH(FLOOR(
  /*DECIMAL COLUMN*/
  ))                                      +NVL(LENGTH(RTRIM(SUBSTR(CN,INSTR(CN,'.')+1),'0')),0) NUMBER_OF_DEGITS,
  NVL(LENGTH(RTRIM(SUBSTR(CN,INSTR(CN,'.')+1),'0')),0) NUMBER_OF_DECIMALS
FROM
  (SELECT
    /*COLUMN NAMES*/
    ,
    TO_CHAR(
    /*DECIMAL COLUMN*/
    ,'FM99999999999999999D99999999999999999','NLS_NUMERIC_CHARACTERS = ''. ''') CN
  FROM S
  );

Comments

Post a Comment

Popular posts from this blog

Split String which has space and replace spaces with "/"

Sample String "ABCD123 ETS, NM" UPDATE TABLE_NAME SET COLUMN_NAME = SUBSTR( 'ABCD123 ETS, NM' , 0 , 4 ) || REPLACE ((SUBSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , '/' )), 5 ,INSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , ',/' )), ',' , 1 ) - 4 )), ' ' , '/' ) || REPLACE (SUBSTR( 'ABCD123 ETS, NM' ,INSTR( 'ABCD123 ETS, NM' , ',' , 1 ) + 2 ), 'NM' , 'NEW MEXICO' ) WHERE CONDITIONS;
Post a Comment
Read more

Extracting Data in a Database Using Python

Extracting data in a database using python. Using Python to extract data in a MySQL table first we need to pip install pymysql Create table along with connection to the mysql server using python import pymysql connection=pymysql.connect(host='host',port=port,user='user',password='password',db='schema') #creating cursor cursor= connection.cursor() #query for create Table TABLES={} TABLES['employees'] = ( "CREATE TABLE employees (" "PersonID int," "LastName varchar(255)," "FirstName varchar(255)," "Address varchar(255)," "City varchar(255)" ")") for name, ddl in TABLES.items(): cursor.execute(ddl) connection.commit() connection.close() Insert Data into the MySQL Table using Python import pymysql # Data set to insert insert_people=[("8","Perera","L.D","123",...
1 comment
Read more