Adding a Number in String in SQL Server

In this article I provide a quick overview of how to add a number in a string in SQL Server. In this article you will take a string and add a number before the string or after the string. To do that we can create various queries using SQL functions. So let's have a look at a practical example of how to add a number in a string in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

The following are the queries to add the first and after number in a string.

Using the SQL Left Function

The Left string function takes two arguments. The first argument is a string value and the second argument is an integer value specifying the length. It returns the first characters of the specified length starting from the left side of the string entered as the first argument.

Example

Declare @Name varchar(20) -- Declare a char Variable

Set @Name='Rohatash' 

select Left(@Name,3)

Output

To Add Number before the String

The example below defines Number before the String using the left function.

Example

In this example we take a string and a number. Using the left function a number will be added to the left of the string.

Declare @Name varchar(20) -- Declare a char Variable

Set @Name='Rohatash'  -- Name

Declare @Number int   -- Declare a int Variable

set @Number=123 -- Number to add in String

select left(@Number,3) + @Name  -- Using left Function

Output

Using SQL Cast Function

Declare @Name varchar(20) -- Declare a char Variable

Set @Name='Rohatash'  -- Name

Declare @Number int   -- Declare a int Variable

set @Number=123 -- Number to add in String

select  cast(@Number as varchar(20)) + @Name    -- Using Cast Function

Output

Using the SQL Right Function

The Right string function takes two arguments. The first argument is a string value and the second argument is an integer value specifying the length. It returns the last characters of the specified length starting from the right side of the string entered as the first argument.

Example

Declare @Name varchar(20) -- Declare a char Variable

Set @Name='Rohatash' 

select Right(@Name,3)

Output

To Add a Number Before the String

The example below defines Number before the String using the left function.

Example

In this example we take a string and number. Using the left function the number will be added to the left of the string.

Declare @Name varchar(20) -- Declare a char Variable

Set @Name='Rohatash'  -- Name

Declare @Number int   -- Declare a int Variable

set @Number=123 -- Number to add in String

select @Name + Right(@Number,3)   -- Using right Function

Output

Using SQL Cast Function

Declare @Name varchar(20) -- Declare a char Variable

Set @Name='Rohatash'  -- Name

Declare @Number int   -- Declare a int Variable

set @Number=123 -- Number to add in String

select @Name + cast(@Number as varchar(20))   -- Using Cast Function

Output