Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Removing part of string before and after specific character using Transact-SQL string functions


Today, one of the developers come to me and asked me the question that is there any T-SQL function that he could use to remove everything before and after a specific character in string. For example, if the table contains the full names of the people in the format as firstname comma surname (Farooq,Basit). He would like to retrieve the first and surnames of people in separate columns of result set.


The easiest solution that comes to my mind for this problem is to use LEFT and REPLACE string function combined CHARINDEX and LEN string function.

To remove the part of string after the specific character, you use these transact-sql string functions as follow:

SELECT LEFT(string_expression, CHARINDEX(expression_to_find, string_expression) - 1)

To remove the part of string before the specific character, you use these transact-sql string functions as follow:

SELECT REPLACE(SUBSTRING(string_expression, CHARINDEX(expression_to_find, string_expression), LEN(string_expression)), string_pattern, string_replacement)


For example, I created the following table that contains the sample dummy data. See screen shot below that shows the format of data in this sample table:


Below is the query that splits the data base on comma(,) in FullName column to FirstName and Surname :

SELECT   [FullName]
	    ,LEFT([FullName], CHARINDEX(',', [FullName]) - 1) AS [Surname]
	    ,REPLACE(SUBSTRING([FullName], CHARINDEX(',', [FullName]), LEN([FullName])), ',', '') AS [FirstName]
FROM    Employee

Example Output


Hope you will like this post…:)


Leave a comment on the original post [, opens in a new window]

Loading comments...