converting varchar value to integer decimal value in sql server

In sql server converting the value of records in a table with data type varchar to integer or decimal so you can get the sum of the value.

create table tm_Salary

(

  ID int,

  Salary_Amount varchar(50)

)

insert into tm_Salary values(1, '32.43');

insert into tm_Salary values(2, '43.33');

insert into tm_Salary values(3, '23.22');

sql Query:

SELECT  
   SUM(cast(Salary_Amount as decimal(4,2))) as Sum_Of_Salary  
FROM tm_Salary

OR

SELECT  
   SUM(cast(isnull(Salary_Amount,0) as decimal(12,2))) as Sum_Of_Salary 
FROM tm_Salary

OR

SELECT  
 SUM(convert(decimal(10,2), Salary_Amount)) as Sum_Of_Salary 
FROM tm_Salary

OR

SELECT 
  SUM(CAST(Salary_Amount AS decimal(6,2))) as Sum_Of_Salary  
FROM tm_Salary


 Result:

sql-select-query

SQL Select Query

SQL SELECT statement is used to retrieve the data from a table in the database. 

sql select query retrieve data from particular column or all columns in the table.  

the query result is called result-sets.

Syntax of SQL SELECT Statement:

SELECT Column1, Column2, Column3, ColumnN

FROM Table_Name

Here, column1, column2...are the fields of a table whose values you want to fetch. 

• Table_Name through which data is retrieved. 
• columns which you want to retrieved the data from one or more column.

  Example:

Consider the EMPLOYEE table having the following records:

If you want to fetch all the fields of EMPLOYEE table, then use the following query:

SELECT * FROM Employee

If you want to fetch Employee_id, First_name, Salary fields of EMPLOYEE table, then use the following query:

SELECT Employee_id, First_name, Salary 

FROM Employee

 

WHERE Clause in SELECT Statement:

The WHERE Clause is used to retrieve the specific information on the basis of condition. the conditional operation give you the exact result whatever you wants.   

For example, when you want to see the information about Employee whose salary not greater than 700000. Retrieving information about all the Employee would increase the processing time for the query.

Example

SELECT First_name, salary

FROM employee

WHERE Salary > 700000;

Result

 

 

sql-is-not-null-and-is-not-empty-string

SQL Is Not Null or Not Empty 

 How to Check for Is Not Null or Not Empty Column in SQL Server SQL

A Not Null means the value in the column is not null (means it has some value in the column). You have to use a clause in SQL IS Not Null.

On the other hand, not empty string is an actual value that can be compared to in a database. You simply use two ticks together.

Create Table First in SQL Server

CREATE TABLE [dbo].[Employee](

      [Employee_id] [int] IDENTITY(1,1) NOT NULL,

      [First_name] [nvarchar](300) NULL,

      [Last_name] [nvarchar](300) NULL,

      [Salary] [decimal](20, 2) NULL,

      [Joining_date] [datetime] NULL,

      [Department] [nvarchar](200) NULL

) ON [PRIMARY]

 Insert Record 
 

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('John', 'Abraham', 1000000.00, 1905-06-07, 'Banking')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Michael', 'Clarke', 800000.00, 1894-06-28, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Michael', 'Clarke', 800000.00, 1894-06-28, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Roy', 'Thomas', 700000.00, 1894-06-27, 'Banking')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Tom', 'Jose', 600000.00, 1894-06-27, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Jerry', 'Pinto', 650000.00, 1894-06-27, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Philip', 'Mathew', 750000.00, 1894-06-28, 'Services')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('TestName1', '123', 650000.00, 1894-06-28, 'Services')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('TestName2', 'Lname%', 600000.00, 1894-06-27, 'Insurance')

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Yamuna', 'Singh',4000.00, NULL, NULL)

INSERT [dbo].[Employee] ([First_name], [Last_name], [Salary], [Joining_date], [Department]) VALUES ('Pankaj', 'Roy', 45000.00, NULL, '')

Show Record

select * from [dbo].[Employee]

 

In this above table, I specifically put in some Department that are both not null and not empty strings.

Get the Not NULL values using the IS Not NULL operator.

select * from [dbo].[Employee]

where [Department] is not null

Result

Get the not empty string using the not equal string.

select * from [dbo].[Employee]

where [Department] != ''

Result

  If you want to combine them to search for the SQL is not  null or not empty string together and retrieve all of the empty strings and nulls all at once, you could do something like this.

select * from [dbo].[Employee]

where (([Department] IS NOT NULL) AND ([Department] != ''))

Result