String Functions

SQL CHARINDEX Function

2015-06-29 by suresh

In SQL Server, CHARINDEX Function is used to find the index position of a specified expression from existing records.

TIP: The index position in CHARINDEX Function will start from 1, Not 0.

SQL CHARINDEX Function Syntax

In SQL Server, The basic syntax of the SQL CHARINDEX Function is:

1 2	SELECT CHARINDEX (ExpressionToFind, ExpressionToSearch, Starting_Position) FROM [Source]

• ExpressionToFind: Please specify the valid Expression to find. CHARINDEX function will search for this expression, if it find the expression then it will return the index position otherwise, it will return 0.
• ExpressionToSearch: Please specify the valid Expression on which to want to perform search operation. The CHARINDEX function will search this expression for ExpressionToFind.
• Starting_Position: This argument is optional. If you specify the starting position then CHARINDEX Function will start searching from this position otherwise, it will start searching from the beginning.

TIP: CHARINDEX Function will return NULL if ExpressionToFind or expressionToSearch is NULL.

In this article we will show you, How to write CHARINDEX Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL CHARINDEX Function Example 1

CHARINDEX Function is used to search for index position of a specific expression from the original string. The following query will show multiple ways to use CHARINDEX function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10 11

DECLARE @ExpressionToSearch varchar(100) SET @ExpressionToSearch = 'Learn SQL Server 2014 with Examples in Tutorial Gateway' --Searching for a existent word --37 SELECT CHARINDEX ('in', @ExpressionToSearch) AS 'SQL CHARINDEX' --Searching from specific position – 20 SELECT CHARINDEX ('in', @ExpressionToSearch, 20) AS 'SQL CHARINDEX' --Searching for a non-existent word –0 SELECT CHARINDEX ('CHARINDEX', @ExpressionToSearch) AS 'SQL CHARINDEX'

OUTPUT

Analysis

Below lines of code is used to declare string variable and assigning the string data.

1 2	DECLARE @ExpressionToSearch varchar(100) SET @ExpressionToSearch = 'Learn SQL Server 2014 with Examples in Tutorial Gateway'

In below statement, We used CHARINDEX function to find the index position of ‘in’ from the variable @ExpressionToSearch. We also assigned new name to that result as ‘SQL CHARINDEX’ using ALIAS Column.

1 2	--Searching for an existent word --37 SELECT CHARINDEX ('in', @ExpressionToSearch) AS 'SQL CHARINDEX'

In the next line, We used the starting position argument inside the CHARINDEX function. It means function will start search for ‘in’ from position 20, reduce the load.

1 2	--Searching from specific position – 20 SELECT CHARINDEX ('in', @ExpressionToSearch, 20) AS 'SQL CHARINDEX'

In the next line, We searched for CHARINDEX, which is not existed inside the variable. Thats why it is returning 0

1 2	--Searching for a non-existent word –0 SELECT CHARINDEX ('CHARINDEX', @ExpressionToSearch) AS 'SQL CHARINDEX'

SQL CHARINDEX Function Example 2

CHARINDEX function also allows you to search for the expressions inside the column values. In this example,We are going to find the index position of an Empty Space inside the Department Name column

T-SQL CODE

1 2 3 4 5

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,CHARINDEX (' ', [DepartmentName]) AS [Space Position] FROM [Employe]

OUTPUT

NOTE: If you observe the second record, Although it has Empty space at multiple position CHARINDEX displayed the value 4. This is because, CHARINDEX will display the index position of a first occurence and it does not care after that.

SQL CHARINDEX Function Example 3

In this example, We are going to find the index position of a @ symbol present in Email column using CHARINDEX function

T-SQL CODE

1 2 3 4 5 6

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,[Email]       ,CHARINDEX ('@', [Email]) AS [@ Position] FROM [Employe]

OUTPUT

NOTE: We can’t use CHARINDEX on Image, Text and NText Data Types.

Thank You for Visiting Our Blog

SQL CONCAT Function

2015-06-29 by suresh

In SQL Server, CONCAT Function is used to combine two or more strings and returns string.

TIP: Unlike + Operator CONCAT Function will convert Null values into Empty string while displaying the result.

SQL CONCAT Function Syntax

In SQL Server, The basic syntax of the SQL CONCAT Function is:

1 2	SELECT CONCAT (String 1,String 2,..,String N) FROM [Source]

In this article we will show you, How to write Concat Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL CONCAT Function Example 1

CONCAT Function simply returns the string by combining two or more strings. The following query will show multiple ways to use Concat function.

T-SQL CODE

1 2 3 4 5 6 7 8 9

DECLARE @String1 varchar(50),         @String2 varchar(50) SET @String1 = 'Learn ' SET @String2 = 'SQL Server' SELECT CONCAT(@String1, @String2) AS 'SQL Concat' --Concating three String Directly SELECT CONCAT('Learn', ' SQL Server',' 2014') AS 'SQL Concat'

OUTPUT

Analysis

Below lines of code is used to declare two string variables and assigning them some data.

1 2 3 4

DECLARE @String1 varchar(50),         @String2 varchar(50) SET @String1 = 'Learn ' SET @String2 = 'SQL Server'

Below statement, Concat function will combine the two variable and produce the string result. We also assigned new name to that result as ‘SQL Contact’ using Alias Column.

1	SELECT CONCAT(@String1, @String2) AS 'SQL Concat'

In the next line, We used Concat function to combine the three string values directly

1 2	--Concating three String Directly SELECT CONCAT('Learn', ' SQL Server',' 2014') AS 'SQL Concat'

SQL CONCAT Function Example 2

CONCAT function also allows you to combine multiple columns. In this example,We are going combine the First name and Last Name columns present in the Employe column to get Full name.

T-SQL CODE

1 2 3 4 5

SELECT [FirstName]       ,[LastName]       ,CONCAT([FirstName],' ', [LastName]) AS [Full Name]       ,[DepartmentName] FROM [Employe]

OUTPUT

TIP: We used this ‘  ‘ to get empty space. If you want comma or something just replace empty space with comma

SQL CONCAT Function Example 3

CONCAT function also allows you to combine columns and string text in one statement. In this example,We are going combine the First name, Last Name and Department name columns present in the Employe column and string text = ‘is working as’.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,CONCAT(         [FirstName],' ',[LastName],' is Working as',' ',[DepartmentName]       ) AS [Description] FROM [Employe]

OUTPUT

Thank You for Visiting Our Blog

SQL LEN Function

2015-06-29 by suresh

In SQL Server, LEN Function is used to find the length of a specified expression. OR

The LEN Function will count the number of characters inside the specified expression and returns the INT results.

SQL LEN Function Syntax

In SQL Server, The basic syntax of the LEN Function is:

1 2	SELECT LEN (String_Expression) FROM [Source]

In this article we will show you, How to write LEN Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL LEN Function Example 1

LEN Function is used to count the number of characters inside the specified expression. The following query will show multiple ways to use LEN function.

T-SQL CODE

1 2 3 4 5 6 7

DECLARE @String_Expression varchar(50) SET @String_Expression = 'Learn SQL Server' SELECT LEN (@String_Expression) AS 'SQL Length' --Finding Length directly SELECT LEN ('Learn SQL Server 2014') AS 'SQL Length'

OUTPUT

Analysis

Below lines of code is used to declare string variable and assigning the string data.

1 2	DECLARE @String_Expression varchar(50) SET @String_Expression = 'Learn SQL Server'

In the below statement, We used LEN function to find the length of a string variable @String_Expression. We also assigned new name to that result as ‘SQL Length’ using ALIAS Column.

1	SELECT LEN (@String_Expression) AS 'SQL Length'

In the next line, We used the LEN Function directly on the string.

1 2	--Finding Length directly SELECT LEN ('Learn SQL Server 2014') AS 'SQL Length'

SQL LEN Function Example 2

LEN function also allows you to find the length of an expressions inside the columns. In this example, We are going to find the length of a [Department Name] and Email length as well.

T-SQL CODE

1 2 3 4 5 6 7

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,LEN([DepartmentName]) AS [Dept Length]       ,[Email]       ,LEN([Email]) AS [Email Length] FROM [Employe]

OUTPUT

SQL LEN Function in WHERE Condition

In this example, We are going to use the LEN function inside the Where Clause. For instance, The following query will check whether the length of a [Department Name] is greater than 10 or not. If the condition is TRUE then, SELECT Statement will display the records.

T-SQL CODE

1 2 3 4 5 6 7 8

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,LEN([DepartmentName]) AS [Dept Length]       ,[Email]       ,LEN([Email]) AS [Email Length] FROM [Employe] WHERE LEN([DepartmentName]) > 10

OUTPUT

Thank You for Visiting Our Blog

SQL LEFT Function

2015-06-29 by suresh

In SQL Server, LEFT Function is used to return left most characters from the specified expression. LEFT function uses its second argument to decide, How many characters it should return.

TIP: The index position in LEFT Function will start from 1, Not 0.

SQL LEFT Function Syntax

In SQL Server, The basic syntax of the SQL LEFT Function is:

1 2	SELECT LEFT (Character_Expression, Value) FROM [Source]

• Character_Expression: Please specify the valid Expression on which to want to perform search operation. LEFT Function will write the left most characters from this expression.
• Value: Please specify, how many characters you want to extract from the Character_Expression.

In this article we will show you, How to write LEFT Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL LEFT Function Example 1

LEFT Function is used to return the specified number of characters from the given string. The following query will show multiple ways to use LEFT function.

T-SQL CODE

1 2 3 4 5 6 7 8

DECLARE @Charcater_Expression varchar(50) SET @Charcater_Expression = 'Learn SQL Server' --Using Positive Integer 3 -- SQL SELECT LEFT (@Charcater_Expression, 5) AS 'SQL LEFT' --Searching from specific position – 20 SELECT LEFT (@Charcater_Expression, 20) AS 'SQL LEFT'

OUTPUT

Analysis

Below lines of code is used to declare string variable and assigning the string data.

1 2	DECLARE @Charcater_Expression varchar(50) SET @Charcater_Expression = 'Learn SQL Server'

In below statement, We used LEFT function to return five leftmost characters from the @Character_Expression variable. We also assigned new name to that result as ‘SQL LEFT’ using ALIAS Column.

1 2	--Using Positive Integer -- SQL SELECT LEFT (@Charcater_Expression, 5) AS 'SQL LEFT'

In the next line, We set the second argument as 20, which is greater than the string length so, LEFT function will return all the characters from the @Character_Expression

1 2	--Searching from specific position – 20 SELECT LEFT (@Charcater_Expression, 20) AS 'SQL LEFT'

SQL LEFT Function Example 2

LEFT function also allows you to select left most characters from the column values. In this example, We are going to return the left four words of all the records present inside the Department Name column

T-SQL CODE

1 2 3 4 5

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,LEFT ([DepartmentName], 4) AS [SQL LEFT] FROM [Employe]

OUTPUT

NOTE: If you observe the second record, there is an Empty space after the Sr.

SQL LEFT Function Example 3

In this example, We are going to find the index position of a @ symbol present in Email column using CHARINDEX function and then we will extract the left most characters up to @ symbol using LEFT Function

T-SQL CODE

1 2 3 4 5 6 7 8 9

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,[Email]       ,LEFT (               [Email]      ,CHARINDEX ('@', [Email]) - 1             ) AS [SQL LEFT] FROM [Employe]

OUTPUT

Analysis:

Below statement will return the index position of the @ symbol. It means, CHARINDEX function will check for the index position of @ symbol in each and every record.

1	CHARINDEX ('@', [Email])

We reduced the index position by 1, using the below statement. If you miss this, output include @ symbol as well

1	CHARINDEX ('@', [Email]) - 1

Thank You for Visiting Our Blog

SQL REPLACE Function

2015-06-29 by suresh

In SQL Server, REPLACE Function is used to replace the existing string with new string value.

TIP: In general, REPLACE Function return VARCHAR output but if one of the input value is NVARCHAR then, it will return NVARCHAR output

SQL REPLACE Function Syntax

In SQL Server, The basic syntax of the SQL REPLACE Function is:

1 2	SELECT REPLACE (String_Expression, String You want to change, Replacing_String) FROM [Source]

• String_Expression: Please specify the valid string to perform search. Replace function will replace portion of a string present in this String_Expression.
• String you want to change: Please specify the valid string you want to change. Whatever you place here, REPLACE Function will be replace it by new string
• Replacing_String: Please specify the valid new string you want to insert into String_Expression.

In this article we will show you, How to write REPLACE Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL REPLACE Function Example 1

REPLACE Function simply replaces the original string with specified new string. The following query will show multiple ways to use Replace function.

T-SQL CODE

1 2 3 4 5 6 7

DECLARE @String_Expression varchar(50) SET @String_Expression = 'Learn T-SQL Server' SELECT REPLACE (@String_Expression, 'T-SQL','SQL') AS 'SQL Replace' --Replacing String Directly SELECT REPLACE ('Learn T-SQL Server 2014', 'T-SQL','SQL') AS 'SQL Replace'

OUTPUT

Analysis

Below lines of code is used to declare string variable and assigning the string data.

1 2	DECLARE @String_Expression varchar(50) SET @String_Expression = 'Learn T-SQL Server'

In below statement, We used REPLACE function to replace the T-SQL with SQL from the variable @String_Expression. We also assigned new name to that result as ‘SQL Replace’ using ALIAS Column.

1	SELECT REPLACE (@String_Expression, 'T-SQL','SQL') AS 'SQL Replace'

In the next line, We used REPLACE function to replace the T-SQL with SQL from ‘Learn T-SQL Server 2014′ string directly

1 2	--Replacing String Directly SELECT REPLACE ('Learn T-SQL Server 2014', 'T-SQL','SQL') AS 'SQL Replace'

SQL REPLACE Function Example 2

REPLACE function also allows you to replace the column values. In this example,We are going to replace the .com with .org from Email column

T-SQL CODE

1 2 3 4 5 6

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,[Email]       ,REPLACE ([Email], 'com','org' ) AS [SQL Replace] FROM [Employe]

OUTPUT

SQL REPLACE Function Example 3

In this example, We are going to replace the @ symbol present in Email column with Empty space using REPLACE function

T-SQL CODE

1 2 3 4 5 6

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,[Email]       ,REPLACE ([Email], '@','  ' ) AS [SQL Replace] FROM [Employe]

OUTPUT

Thank You for Visiting Our Blog

SQL REVERSE Function

2015-06-29 by suresh

In SQL Server, REVERSE Function is used to reverse the specified expression.

SQL REVERSE Function Syntax

In SQL Server, The basic syntax of the REVERSE Function is:

1 2	SELECT REVERSE (String_Expression) FROM [Source]

In this article we will show you, How to write REVERSE Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL REVERSE Function Example 1

REVERSE Function is used to reverse the given string. The following query will show multiple ways to use REVERSE function.

T-SQL CODE

1 2 3 4 5 6 7

DECLARE @String_Expression varchar(50) SET @String_Expression = 'SQL Server' SELECT REVERSE (@String_Expression) AS 'SQL Reverse' --String Reverse directly SELECT REVERSE ('SQL Server 2014') AS 'SQL Reverse'

OUTPUT

Analysis

Below lines of code is used to declare string variable and assigning the string data.

1 2	DECLARE @String_Expression varchar(50) SET @String_Expression = 'SQL Server'

In the below statement, We used REVERSE function to reverse the string variable @String_Expression. We also assigned new name to that result as ‘SQL Reverse’ using ALIAS Column.

1	SELECT REVERSE (@String_Expression) AS 'SQL Reverse'

In the next line, We used the REVERSE Function directly on the string.

1 2	--String Reverse directly SELECT REVERSE ('SQL Server 2014') AS 'SQL Reverse'

SQL REVERSE Function Example 2

REVERSE function also allows you to reverse the expressions inside the columns. In this example, We are going to reverse all the records present in [Department Name].

T-SQL CODE

1 2 3 4 5

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,REVERSE ([DepartmentName]) AS [SQL Reverse] FROM [Employe]

OUTPUT

Thank You for Visiting Our Blog

SQL RIGHT Function

2015-06-29 by suresh

In SQL Server, RIGHT Function is used to return right most characters from the specified expression. RIGHT function uses its second argument to decide, How many characters it should return.

TIP: The index position in RIGHT Function will start from 1, Not 0.

SQL RIGHT Function Syntax

In SQL Server, The basic syntax of the SQL RIGHT Function is:

1 2	SELECT RIGHT (Character_Expression, Value) FROM [Source]

• Character_Expression: Please specify the valid Expression on which to want to perform search operation. RIGHT Function will write the right most characters from this expression.
• Value: Please specify, how many characters you want to extract from the Character_Expression.

In this article we will show you, How to write RIGHT Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL RIGHT Function Example 1

RIGHT Function is used to return the specified number of rightmost characters from the given string. The following query will show multiple ways to use RIGHT function.

T-SQL CODE

1 2 3 4 5 6 7 8

DECLARE @Charcater_Expression varchar(50) SET @Charcater_Expression = 'Learn SQL Server' --Using Positive Integer 6 SELECT RIGHT (@Charcater_Expression, 6) AS 'SQL RIGHT' --Searching from specific position – 20 SELECT RIGHT (@Charcater_Expression, 20) AS 'SQL RIGHT'

OUTPUT

Analysis

Below lines of code is used to declare string variable and assigning the string data.

1 2	DECLARE @Charcater_Expression varchar(50) SET @Charcater_Expression = 'Learn SQL Server'

In below statement, We used RIGHT function to return six rightmost characters from the @Character_Expression variable. We also assigned new name to that result as ‘SQL RIGHT using ALIAS Column.

1 2	--Using Positive Integer 6 SELECT RIGHT (@Charcater_Expression, 6) AS 'SQL RIGHT'

In the next line, We set the second argument as 20, which is greater than the string length so, RIGHT function will return all the characters from the @Character_Expression

1 2	--Searching from specific position – 20 SELECT RIGHT (@Charcater_Expression, 20) AS 'SQL RIGHT'

SQL RIGHT Function Example 2

RIGHT function also allows you to select right most characters from the column values. In this example, We are going to return the rightmost nine words of all the records present inside the Email column

T-SQL CODE

1 2 3 4 5 6

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,[Email]       ,RIGHT ([Email], 9) AS [SQL RIGHT] FROM [Employe]

OUTPUT

If you observe the above result, RIGHT Function is not getting the domain names perfectly because we are using fixed value. In the next example we will show you, How to use dynamic value as a second argument to return the domain names accurately.

Find Domain Names in Email using SQL RIGHT Function

In this example, We are going to find the domain names present in Email column using SQL RIGHT Function.

T-SQL CODE

1 2 3 4 5 6 7 8 9

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,[Email]       ,RIGHT (               [Email]              ,(LEN([Email]) - CHARINDEX ('@', [Email]))              ) AS [SQL RIGHT] FROM [Employe]

OUTPUT

Analysis:

Below statement will find the length of Email using LEN Function

1	LEN([Email])

Below statement will return the index position of the @ symbol. It means, CHARINDEX function will check for the index position of @ symbol in each and every record.

1	CHARINDEX ('@', [Email])

We subtracted the index position from the Length of a string using the below statement.

1	(LEN([Email]) - CHARINDEX ('@', [Email]))

Thank You for Visiting Our Blog

SQL STUFF Function

2015-06-30 by suresh

In SQL Server, STUFF Function is used to insert one string inside another string.

SQL STUFF Function Syntax

In SQL Server, The basic syntax of the SQL STUFF Function is:

1 2	SELECT STUFF (Character_Expression, Starting_Position, Length, New_String) FROM [Source]

• Character_Expression: Please specify the valid string on which you want to insertNew_String.
• Starting_Position: Please specify, from which index position you want to start inserting the New_String characters.
• Length: Please specify, how many characters you want to delete from theCharacter_Expression. STUFF Function will start at Starting_Position and delete the specified number of characters from the Character_Expression
• New_String: Please specify the new string you want to stuff inside the Character_Expression at Starting_Position

In this article we will show you, How to write STUFF Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL STUFF Function Example 1

STUFF Function is used to stuff the new string inside the old string. The following query will show multiple ways to use STUFF function.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10 11 12 13 14

DECLARE @Character_Expression varchar(50) SET @Character_Expression = 'Learn Server' -- Starting Position = 6 and End Position = 0 SELECT STUFF (@Character_Expression, 6, 0, ' SQL') AS 'SQL STUFF' -- Starting Position = 7 and End Position = 6 SELECT STUFF (@Character_Expression, 7, 6, 'SQL') AS 'SQL STUFF' -- Starting Position = 1 and End Position = 5 SELECT STUFF (@Character_Expression, 1, 5, '') AS 'SQL STUFF' -- Starting Position = 20 and End Position = 5 SELECT STUFF (@Character_Expression, 20, 5, 'SQL ') AS 'SQL STUFF'

OUTPUT

Analysis

Below lines of code is used to declare string variable and assigning the string data.

1 2	DECLARE @Character_Expression varchar(50) SET @Character_Expression = 'Learn Server'

In the below statement, We assigned starting position = 6, ending position = 0, and  fourth argument is SQL so, STUFF function will delete 0 characters from index position 6 and inserts SQL at index position 6

1 2	-- Starting Position = 6 and End Position = 0 SELECT STUFF (@Character_Expression, 6, 0, ' SQL') AS 'SQL STUFF'

In the below statement, We assigned starting position = 7, ending position = 6, and  fourth argument is SQL so, STUFF function will delete five characters from index position 7 and inserts SQL at that position.

1 2	-- Starting Position = 7 and End Position = 6 SELECT STUFF (@Character_Expression, 7, 6, 'SQL') AS 'SQL STUFF'

In the below statement, We assigned starting position = 1, ending position = 5 ,and  fourth argument is empty so, STUFF function will delete characters from index position 1 to 5 and return remaining string.

1 2	-- Starting Position = 1 and End Position = 5 SELECT STUFF (@Character_Expression, 1, 5, '') AS 'SQL STUFF'

In the next line, We set the second argument as 20, which is greater than the string length so, STUFF function will return NULL

1 2	-- Starting Position = 20 and End Position = 5 SELECT STUFF (@Character_Expression, 20, 5, 'SQL ') AS 'SQL STUFF'

SQL STUFF Function Example 2

STUFF function also allows you to add new string to old string in the column values. In this example, We are going to insert [LastName] to the Email column at index position 1.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10 11

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,[Email]       ,STUFF (               [Email]              ,1      ,0      ,[LastName]              ) AS [SQL SUBSTRING] FROM [Employe]

OUTPUT

If you observe the above result, STUFF Function did not removed any characters from the Emails, it just added the last name to that. This is because, We used 0 as third argument. If you want to remove few characters then replace 0 with positive value or just see next example.

Changing Email Ids using SQL STUFF Function

In this example, We are going to change the email ids present in Email column using SQL STUFF Function. The following query will remove the characters before the @ symbol and insert the [Last Name] column data.

T-SQL CODE

1 2 3 4 5 6 7 8 9 10 11

SELECT [FirstName]       ,[LastName]       ,[DepartmentName]       ,[Email]       ,STUFF (                [Email]               ,1       ,CHARINDEX('@',[Email]) - 1       ,[LastName]             ) AS [SQL SUBSTRING] FROM [Employe]

OUTPUT

Analysis:

Below statement will return the index position of the @ symbol. It means, CHARINDEX Function will check for the index position of @ symbol in each and every record. Next, We subtracted 1 from the index position because we just want to replace characters before this symbol.

1	CHARINDEX ('@', [Email]) - 1

Below statement will delete the characters from index position 1 to @ symbol and  then insert [Last Name] data in that place using STUFF

1 2 3 4 5 6

STUFF (        [Email]       ,1       ,CHARINDEX('@',[Email]) - 1       ,[LastName]      ) AS [SQL SUBSTRING]

Thank You for Visiting Our Blog

STUFF Function

  

In this article I am trying to illustrate a vary useful function of SQL Server called STUFF.

STUFF Function

Stuff is a T-SQL Function used to delete a specified length of characters within a string and replace with another set of characters.

  

Syntax:

STUFF(Character_expression1, start, length, Character_expression2)

Character_expression1:

Represents the string in which the stuff function is to be applied.

Start:

It indicates the starting position of the character in the string of Character_expression1.

Length:

The length of the characters, which need to be replaced.

Charcter_expression2:

 It is the string that will be replaced to the start position.

Example:

SELECT STUFF('joydeep', 2, 3, 'xxxxx')

---------------

jxxxxxeep

Here "o" is the starting position 2, so from position 2 it takes 3 character "oyd" and replace it by "xxxxx".