Skip to main content

CONCAT FUNCTION IN SQL SERVER 2012

Concat function is used concatnating the values among the different arguments.

Syntax:
Concat([String1],[String2]..[StringN]); 

DECLARE @FName VARCHAR(50),@LName VARCHAR(50);
SET @FName='Rakesh' ;
SET @LName='Kalluri';
SELECT CONCAT(@FName,' ', @LName) as [FullName];
Output: Rakesh Kalluri

--Before SQL 2012 Versions we can use like this way
DECLARE @FName VARCHAR(50),@LName VARCHAR(50);
SET @FName='Rakesh' ;
SET @LName='Kalluri';
SELECT @FName+' '+@LName as [FullName];
Output: Rakesh Kalluri

--But Before SQL 2012 Versions there are 2 problems when we contacting.
--1.Need to replace NULL Value to Empty.if we directly concat NULL Value the Entire result also NULL.

--Before 2012:
DECLARE @FName VARCHAR(50),@LName VARCHAR(50);
SET @FName='Rakesh' ;
SELECT @FName+' '+@LName as [FullName];
Output: NULL

--From 2012 (Null values are implicitly converted to empty)
DECLARE @FName VARCHAR(50),@LName VARCHAR(50);
SET @FName='Rakesh' ;
SELECT CONCAT(@FName,' ', @LName) as [FullName];
Output: Rakesh

--2.Need to Convert String Datatype

--Before 2012:
DECLARE @FName VARCHAR(50),@Id INT;
SET @FName='Rakesh' ;
SET @Id=1
SELECT @FName+' '+@Id as [Details];
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'Rakesh ' to data type int.

--Problem Solving Before 2012 Versions
SELECT @FName+' '+CAST(@Id AS VARCHAR(50)) as [Details];
Output: Rakesh 1

--From 2012
DECLARE @FName VARCHAR(50),@Id INT;
SET @FName='Rakesh' ;
SET @Id=1
SELECT CONCAT(@FName,' ', @Id) as [Details];
Output: Rakesh 1


Labels:

Comments

Post a Comment

Popular posts from this blog

SET ROWCOUNT

Stop the processing after the number of rows has been affected. Example: Step-1: Create the Table and insert some sample data. CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' In above Insert Statement we inserted 5 sample records. Step-2: Using SET ROWCOUT SET ROWCOUNT 3 ; SELECT * FROM STUDENT ORDER BY ID DESC It will affected 3 rows after that processing has been stopped. Step-2: Using SET ROWCOUT 0 SET ROWCOUNT 0 ; SELECT * FROM STUDENT ORDER BY ID DESC ...
7 comments
Read more

Coalesce function

Coalesce function returns the first non-null value among the arguments. Syntax: Coalesce (expression [,..n]) Here is example using Coalesce function Example 1 DECLARE @Str1 varchar ( 10 ), @str2 varchar ( 20 ), @Str3 varchar ( 20 ) SET @Str2 = 'Sql' , @Str3 = 'Server' SELECT COALESCE ( @Str1 , @str2 , @Str3 ) As [Coalesce] In above example @Str2 value is ‘Sql’ , @str3 value is ‘Server’  and @str1 values is Null because it not assigned any value . Output: It return’s “Sql” because Coalesce function return’s first non null value. Example 2: Coalesce in select statement. IF OBJECT_ID ( 'Employee' , 'U' ) IS NOT NULL DROP TABLE Employee CREATE TABLE Employee (   ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY ,   NAME VARCHAR ( 20 ),   SALARY INT ) INSERT INTO Employee   ( NAME , SALARY ) VALUES ( 'Rakesh' , 5000 ),(NULL, 6000 ),( 'Naresh...
1 comment
Read more

Simple Trick Using ISNULL

Simple Trick Using ISNULL: Step-1: Create Sample Student Table. CREATE TABLE Student ( Id INT IDENTITY ( 1 , 1 ) PRIMARY KEY , StudentName VARCHAR ( 100 ) NOT NULL, LivingLocation VARCHAR ( 100 ) NULL ) Step-2 :Insert Some Sample Data INSERT INTO Student SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' Step-3 : Select Data from Student Table SELECT * FROM Student If we observe above data of student table we have student id 4 record LivingLocation is NULL. Step -4 :Select Data from Student Table With Where Clause. SELECT * FROM Student WHERE LivingLocation = 'Hyderabad' In the above query we are included the where clause w...
3 comments
Read more