File Validation in SQL Server (xp_fileexist)

With the help of xp_fileexist SP, we will check the file in the folder exists or not.

Let’s see the example.

   

 

In this folder, I have a file.

Using the below script we will check it.

DECLARE @Is_File_Exists INT,                                 @File_name varchar(100); SET @File_name ='J:\SSIS1\Source\Product\Product_File.txt' EXEC Master.dbo.xp_fileexist @File_name, @Is_File_Exists OUT IF @Is_File_Exists = 1 BEGIN                 PRINT 'File '+ @File_name + ' Exists...' END ELSE BEGIN                 PRINT 'File  '+@File_name+ '  Does not Exists...' END

Executing this script

    

Now, check with a not existing file.

Product_File_1.txt does not exist in the above folder.

DECLARE @Is_File_Exists INT,                                 @File_name varchar(100); SET @File_name ='J:\SSIS1\Source\Product\Product_File_1.txt' EXEC Master.dbo.xp_fileexist @File_name, @Is_File_Exists OUT IF @Is_File_Exists = 1 BEGIN                 PRINT 'File '+ @File_name + ' Exists...' END ELSE BEGIN                 PRINT 'File  '+@File_name+ '  Does not Exists...' END

Running now.    

Trim Function in sql server 2017

The SQL trim function removes the space character char (32) or other specified characters from the start or end of a string. It is the combination of RTRIM and LTRIM Function.

·         LTRIM () function is used to truncate all leading blanks or white-spaces from the left side of the string.

·         RTRIM () function is used to truncate all trailing blanks or white-spaces from the right side of the string.

Select  TRIM('  Bagesh Kumar Singh    ') Select LTRIM('  Bagesh Kumar Singh    ') Select RTRIM('  Bagesh Kumar Singh    ')

     

       

Trim can be used to remove specific characters from both sides of a String.

Syntax:

TRIM ([characters FROM] string)

See the example

SELECT TRIM('~`!@#$%^&*?<>?|\' FROM 'Bagesh Kumar Singh')  As Full_Name SELECT TRIM('~`!@#$%^&*?<>?|\' FROM '!@#$%^ Bagesh Kumar Singh') As Full_Name SELECT TRIM('~`!@#$%^&*?<>?|\' FROM '!@#$%^ Bagesh Kumar Singh&*?<>?|\')  As Full_Name SELECT TRIM('~`!@#$%^&*?<>?|\' FROM 'Bagesh ~`!@#$%^&*?<>?|\ Kumar Singh') As Full_Name

       

     

We can use characters also see example

STRING_ESCAPE Function in sql server 2016

Escapes special characters in texts and returns text with escaped characters. STRING_ESCAPE is a deterministic function, introduced in SQL Server 2016.

Syntax

STRING_ESCAPE (text, type) 

Text: it is an nvarchar expression representing the object that should be escaped.

Type: Escaping rules that will be applied. Currently the value supported is 'Json'.

See the example:

Return Types

Nvarchar (max) text with escaped special and control characters. Currently STRING_ESCAPE can only escape JSON special characters shown in the following tables.

Special character	Encoded sequence
Quotation mark (")	\"
Reverse solidus (\)	\\
Solidus (/)	\/
Backspace	\b
Form feed	\f
New line	\n
Carriage return	\r
Horizontal tab	\t
Control character	Encoded sequence
CHAR(0)	\u0000
CHAR(1)	\u0001
...	...
CHAR(31)	\u001f

See the example

SELECT     STRING_ESCAPE('[''    This is a special / "message" /'']', 'json') AS     escapedJson; SELECT STRING_ESCAPE('\   /  \\    "     ', 'json') AS escapedText;

 

CONCAT_WS() function In SQL Server 2017

Using CONCAT_WS() is simple and straight forward. We have to specify the separator in the form of char, nchar, varchar or nchar as the first argument. Then we can pass on the variables to concatenate in the subsequent arguments. This function will ignore NULL.

CONCAT_WS indicates concatenate with separator

Syntax

CONCAT_WS (separator, argument1, argument2 [, argumentN]...)

See the example

SELECT CONCAT_WS(',','Bagesh', 'Kumar', 'Singh') AS Full_Name; SELECT CONCAT_WS('-','Bagesh', 'Kumar', 'Singh') AS Full_Name; SELECT CONCAT_WS(',','Bagesh', 'Kumar', null,null,'Singh') AS Full_Name;

 

Web scraping

Web scraping is a technique to extract data from different websites, manipulate the data into a structured format, and then save the data to local files for consumption and reporting. We've all probably done some form of web scraping in the past even though we may not have known it at the time.  

With help of R language or python, we can read the web data. 

Arithmetic overflow error converting numeric to data type numeric in SQL Server

This error comes when we try to store an out-of-range floating point value into a numeric variable. For example, if NUMERIC or DECIMAL variable is defined as NUMERIC(5,2) than the maximum value it can hold is 999.99, if we try to store something like 999.999 then it will throw "Arithmetic overflow error converting numeric to data type numeric". One of the common reasons for this error is the ignorance and misunderstanding of the NUMERIC data type.

See the example

Output

Arithmetic overflow error converting numeric to data type numeric.

View the Stored Procedure’s Definition

We can view the stored procedure’s Definition in two way

·         SQL Server Management Studio GUI

·         Transact-SQL

SQL Server Management Studio GUI

Go to DatabaseàDatabase_nameàProgrammability à Stored Proceduresà SP nameàRight click and select àScript Stored procedure as/Modify à create toàNew Query Editor Window

   

A query window will be open as below.

 

With the help of SQL script we can find the definition.

·         sp_helptext

·         Information_Schema.Routines

·         Sys.Sql_Modules

SP_helptext

Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

Syntax

sp_helptext 'sp_name_with_schema'

 sp_helptext 'dbo.uspGetBillOfMaterials'

Default schema is dbo.

See the example

 

Information_Schema.Routines

INFORMATION_SCHEMA.ROUTINES is a system information schema view. This system view can be used to fetch all the details about the stored procedure like name, definition / source code, database schema, created and modified date, etc...

SELECT     ROUTINE_NAME,     ROUTINE_DEFINITION ,     ROUTINE_SCHEMA FROM     INFORMATION_SCHEMA.ROUTINES WHERE     ROUTINE_TYPE='PROCEDURE' AND     ROUTINE_NAME LIKE '%uspGetBillOfMaterials%'

       

In the Routine Definition column, we will get the definition of the SP.

Sys.Sql_Modules

sys.sql_modules is a system object catalog view. This system view can be used to fetch the user-defined programmability object’s definition/source code.sys.sql_modules is a system object catalog view. This system view can be used to fetch the user-defined programmability object’s definition / source code.

Select Object_Name(Object_ID) as SP_Name, definition from Sys.Sql_Modules  where   Object_Name(Object_ID)  LIKE '%uspGetBillOfMaterials%'

   

Definition column we will get the definition of the SP.