Friday, December 17, 2010

How to select left and right substrings in SQL Server

Do you need to select part of a substring in SQL Server's Transact SQL? There are three useful functions for doing this - Left, Right andSubstring. The Transact SQL Left and Right functions are used to truncate strings after a specified number of characters from either the left side or the right side of the string, respectively. Both the Left and Right functions need two arguments to be supplied - the string to be truncated, and the length of the string that should be retained. To show an example of the Left function, the SQL statement below will return "sq": SELECT Left('sql server', 2)

The example below uses the Left function to create a four character DataCode column from the first four characters of the DataDescription column of the FinancialData database table: SELECT DataID, DataDescription,Left(DataDescription, 4) AS DataCode FROM FinancialData ORDER BY DataID By contrast, the Right Transact SQL function truncates a string from the right side of that string. This example creates a DataCode column that is based on the final three characters of the DataDescription column: SELECT DataID, DataDescription, Right(DataDescription, 3) AS DataCode FROM FinancialData ORDER BY DataID To return part of a string, use the Substring function.

No comments:

Post a Comment