Adding a Number in String in SQL Server

Introduction

In this article, I provide a quick overview of adding a number in a string in an 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 look at a practical example of adding a number to a SQL Server String. 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 numbers 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 size 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 a Number before the String

The example below defines the 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 the number before the String using the left function.

Example

In this example, we take a string and a number. The number will be added to the left of the string using the left 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 + 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

Conclusion

In this article, we learned how to add a number using different functions with code examples in String SQL Server. Continue reading a detailed article about Strings in SQL Server here: String in SQL Server.