Monday, April 20, 2015

ISNUMERIC() FUNCTION IN SQL SERVER WITH EXAMLPES

  • The ISNUMERIC function can be used for safe coding by checking the string data prior to CONVERT/CAST to numeric. The ISNUMERIC function as filter is important in data cleansing when internal/external feeds are loaded into the database or data warehouse. 
SYNTAX:

                 ISNUMERIC( expression )

Expression:
           expression is the value to test whether it is a numeric value.

Note:
  • The ISNUMERIC function returns 1, if the expression is a valid number.
  • The ISNUMERIC function returns 0, if the expression is NOT a valid number.
numeric datatypes:

  int,numeric,bigint,money,smallint,smallmoney,tinyint,float,decimal,real

SELECT ISNUMERIC('')------0.  This is understandable, but your logicmay                                              want    to default these to zero.
SELECT ISNUMERIC(' ')--0.  This is understandable, but your logic may want to default these to zero.

SELECT ISNUMERIC('%')--0.

SELECT ISNUMERIC('1%')--0.

SELECT ISNUMERIC('e')--0.
SELECT ISNUMERIC('  ')--1.  --Tab.
SELECT ISNUMERIC(CHAR(0x09))--1.  --Tab.
SELECT ISNUMERIC(',')--1.
SELECT ISNUMERIC('.')--1.
SELECT ISNUMERIC('-')--1.
SELECT ISNUMERIC('+')--1.
SELECT ISNUMERIC('$')--1.
SELECT ISNUMERIC('\')--1.  '
SELECT ISNUMERIC('e0')--1.
SELECT ISNUMERIC('100e-999')--1.  No SQL-Server datatype could hold this number, though it is real.
SELECT ISNUMERIC('3000000000')--1.  This is bigger than what an Int could hold, so code for these too.
SELECT ISNUMERIC('1234567890123456789012345678901234567890')--1.  Note: This is larger than what the biggest Decimal(38) can hold.
SELECT ISNUMERIC('- 1')--1.
SELECT ISNUMERIC('  1  ')--1.
SELECT ISNUMERIC('True')--0.
SELECT ISNUMERIC('1/2')--0.  No love for fractions.

EXAMPLES:

 
USE AdventureWorks2012;
GO
SELECT City, PostalCode
FROM Person.Address 
WHERE ISNUMERIC(PostalCode)<> 1;
GO
 
-- SQL Server ISNUMERIC & CASE quick usage examples - t sql isnumeric and case functions
-- T-SQL IsNumber, IsINT, IsMoney - filtering numeric data from text columns
SELECT ISNUMERIC('$12.09'), ISNUMERIC('12.09'), ISNUMERIC('$'),ISNUMERIC('Alpha')
--          1                 1                 1                             0
SELECT ISNUMERIC('-12.09'), ISNUMERIC('1209')ISNUMERIC('1.0e9'),ISNUMERIC('A001')
--          1                 1                 1                             0


---isnumeric() function with case 

SELECT   TOP (4) AddressID,
                   City,
                   PostalCode,
                   CASE
                     WHEN ISNUMERIC(PostalCode) = 1 THEN 'Y'
                     ELSE 'N'
                   END AS IsZipNumeric
FROM     AdventureWorks2008.Person.Address
ORDER BY NEWID()
/* AddressID   City           PostalCode  IsZipNumeric
27625       Santa Monica      90401       Y
23787       London            SE1 8HL     N
24776       El Cajon          92020       Y
22120       Wollongong        2500        Y    */
 
------------
-- SQL ISALPHANUMERIC check
------------
-- SQL not alphanumeric string test - sql patindex pattern matching
SELECT DISTINCT LastName
FROM   AdventureWorks.Person.Contact
WHERE  PATINDEX('%[^A-Za-z0-9]%',LastName) > 0
GO

/* Partial results

LastName
Mensa-Annan
Van Eaton
De Oliveira
*/
-- SQL ALPHANUMERIC test - isAlphaNumeric
SELECT DISTINCT LastName
FROM   AdventureWorks.Person.Contact
WHERE  PATINDEX('%[^A-Za-z0-9]%',LastName)= 0
GO
/* Partial results
LastName
Abbas
Abel
Abercrombie
*/

-- When left 3 characters satisfy isnumeric test, we convert to int
SELECT
  ProductName,
  QuntityInPackage=convert(int,left(QuantityPerUnit,3))
FROM Products
WHERE ISNUMERIC(left(QuantityPerUnit,3))=1
ORDER BY ProductName


-- Canadian & UK zipcodes would not be numeric - NULL also not numeric (function yields 0)
USE pubs;
SELECT
  Zip=zip,
  [Numeric = 1] = ISNUMERIC(zip)
FROM authors

------------
-- Using IsNumeric with IF...ELSE conditional construct
------------
DECLARE @StringNumber varchar(32)
SET @StringNumber = '12,000,000'
IF EXISTS( SELECT * WHERE ISNUMERIC(@StringNumber) = 1)
      PRINT 'VALID NUMBER: ' + @StringNumber
ELSE
    PRINT 'INVALID NUMBER: ' + @StringNumber
GO
-- Result: VALID NUMBER: 12,000,000

DECLARE @StringNumber varchar(32)
SET @StringNumber = '12-34'
IF EXISTS( SELECT * WHERE ISNUMERIC(@StringNumber) = 1)
      PRINT 'VALID NUMBER: ' + @StringNumber
ELSE
    PRINT 'INVALID NUMBER: ' + @StringNumber
GO


-- Result: INVALID NUMBER: 12-34

- Alternate numeric test with like
-- SQL zipcode test - SQL test numeric - SQL CASE function
SELECT   TOP 5 CompanyName,
               City=City+', '+Country,
               PostalCode,
               [IsNumeric] =
               CASE
                   WHEN PostalCode like '[0-9][0-9][0-9][0-9][0-9]'
                     THEN '5-Digit Numeric'
                   ELSE 'Not 5-Digit Numeric'
               END
FROM     Northwind.dbo.Suppliers
ORDER BY Newid()
GO
/* Results

CompanyName             City                    PostalCode        IsNumeric
Escargots Nouveaux      Montceau, France        71300 5-Digit     Numeric
Norske Meierier         Sandvika, Norway        1320  Not 5-Digit Numeric
Pavlova, Ltd.           Melbourne, Australia    3058  Not 5-Digit Numeric
Zaanse Snoepfabriek     Zaandam, Netherlands    9999 ZZ           Not 5-Digit Numeric
Exotic Liquids          London, UK              EC1 4SD           Not 5-Digit Numeric
*/




 
Posted by at No comments:

Monday, April 13, 2015

BASED ON PARTITION_ID VALUE TO DYNAMICALLY CHANGE DATABASE NAME IN SQL SERVER

                                                    

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_Party_for_Form_mlp_stg]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_Party_for_Form_mlp_stg]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO   

    
CREATE PROCEDURE [dbo].[sp_insert_Party_for_Form_mlp_stg]
                     (@partition_id int)
                     --,@database_name VARCHAR(20))    
AS    
BEGIN    
BEGIN TRY    
SET NOCOUNT ON; 

  DECLARE @SQL NVARCHAR(4000) ='' 
  DECLARE @SQL_INSERT NVARCHAR(4000)=''
  DECLARE @database_name VARCHAR(20)

  SELECT @database_name=CASE WHEN @partition_id=1 THEN 'Multi_Line_Policy'
                             WHEN @partition_id=2 THEN 'Multi_Line_Policy_2'
                             WHEN @partition_id=3 THEN 'Multi_Line_Policy_3'
                             WHEN @partition_id=6 THEN 'Multi_Line_Policy_6'
                             END
   
SET @SQL=@SQL+'DELETE FROM  '+@database_name+'.dbo.[Party] WHERE EXISTS(SELECT 1 FROM 
                tempdb.dbo.tmp_Party_STG WHERE Party_id=[Party].Party_id)'

--PRINT @SQL
EXEC (@SQL)

SET @SQL_INSERT=@SQL_INSERT+'SET IDENTITY_INSERT '+@database_name+'.dbo.[Party] ON
                              INSERT INTO  '+@database_name+'.dbo.[Party](   party_id
                                                                            ,party_type_id
                                                                            ,created_date
                                                                            ,created_user
                                                                            ,modified_date
                                                                            ,modified_user
                                                                            ,valid_flag
                                                                              )
                                                                     SELECT   party_id
                                                                            ,party_type_id
                                                                            ,created_date
                                                                            ,created_user
                                                                            ,modified_date
                                                                            ,modified_user
                                                                            ,valid_flag
                                                                    FROM tempdb.dbo.tmp_Party_STG WITH(NOLOCK)
                                                    SET IDENTITY_INSERT '+@database_name+'.dbo.[Party] OFF'                                                      



  --print @SQL_INSERT

  EXEC (@SQL_INSERT)

  END TRY
    BEGIN CATCH
   
        DECLARE @ErrorMessage NVARCHAR(MAX),@ErrorNumber INT,@ErrorSeverity INT,@ErrorState INT
        SET @ErrorMessage = ERROR_MESSAGE()
        SET @ErrorNumber = ERROR_NUMBER()
        SET @ErrorSeverity = ERROR_SEVERITY()
        SET @ErrorState = ERROR_STATE()
       
        RAISERROR( @ErrorMessage,@ErrorSeverity,@ErrorState );    
   
    END CATCH
END   
   
Posted by at No comments:

Thursday, April 2, 2015

HOW TO SPLITE COMMA SEPERATED STRING AND INSERT INTO TEMP TABLE USING PROCEDURE

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[splite_insert_into_table]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].splite_insert_into_table
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].splite_insert_into_table (@autostates_in_mlp NVARCHAR(4000))
AS
BEGIN

       DECLARE @OrderID varchar(10), @Pos int

    SET @autostates_in_mlp = LTRIM(RTRIM(@autostates_in_mlp))+ ','
    SET @Pos = CHARINDEX(',', @autostates_in_mlp, 1)
    --PRINT @autostates_in_mlp
    --PRINT @Pos


     IF OBJECT_ID('tempdb..#temp_auto_states_in_ML') IS NOT NULL
    DROP TABLE #temp_auto_states_in_ML

    CREATE TABLE #temp_auto_states_in_ML(state_cd CHAR(2))
    IF REPLACE(@autostates_in_mlp, ',', '') <> ''
    BEGIN
        WHILE( @Pos > 0)
        BEGIN
            SET @OrderID = LTRIM(RTRIM(LEFT(@autostates_in_mlp, @Pos - 1)))--WY
            IF @OrderID <> ''
            BEGIN
                INSERT INTO #temp_auto_states_in_ML (state_cd)
                VALUES (CAST( @OrderID AS VARCHAR(10))) --Use Appropriate conversion
            END
            SET @autostates_in_mlp = RIGHT(@autostates_in_mlp, LEN(@autostates_in_mlp) - @Pos)
            SET @Pos = CHARINDEX(',', @autostates_in_mlp, 1)

END
    SELECT * FROM  #temp_auto_states_in_ML
END

END

TESTING :
EXEC splite_insert_into_table @autostates_in_mlp='WY'
EXEC  splite_insert_into_table  @autostates_in_mlp='WY,NA,BA,NA'

--------------------another simple  way

DECLARE @autostates_in_mlp VARCHAR(256)
SET @autostates_in_mlp='WY,NA'
DECLARE @starting_position INT
   
     DECLARE @Previous_position INT

     SET @starting_position=1
     SET @Previous_position=1
      SET @autostates_in_mlp = LTRIM(RTRIM(@autostates_in_mlp))+ ','

      SELECT @starting_position= CHARINDEX(',',@autostates_in_mlp,@Previous_position)

    IF OBJECT_ID('tempdb..#temp_auto_states_in_ML') IS NOT NULL
    DROP TABLE #temp_auto_states_in_ML

    CREATE TABLE #temp_auto_states_in_ML(state_cd CHAR(2))


WHILE (@starting_position>0)
BEGIN
     INSERT INTO #temp_auto_states_in_ML(state_cd)
     SELECT SUBSTRING (@autostates_in_mlp,@Previous_position,@starting_position-@Previous_position)

     SET @Previous_position=@starting_position+1
     SELECT @starting_position = CHARINDEX (',',@autostates_in_mlp,@Previous_position)
END



           
Posted by at No comments:
Subscribe to: Posts (Atom)