T-SQL string operations LEFT RIGHT CHARINDEX SUBSTRING

The LEFT function starts BEFORE the left-most character of a string and moves to the right, while the RIGHT function starts AFTER the right-most character and moves inwards to the left.

SELECT RIGHT(‘HELLO WORLD’, 3);
RLD

SELECT LEFT(‘HELLO WORLD’, 3);
HEL

CHARINDEX function accepts two arguments.
The first argument is the character you are searching for; the second is the string.
It will return the first index position that the character passed into the first argument is within the string.

Now let’s use the CHARINDEX function to find the position of the space in this string:

SELECT CHARINDEX(‘ ‘,’Hello World’);
6

SUBSTRING function accepts three arguments.
The string, a start position and how many characters it will “step over”.

SELECT SUBSTRING(‘HELLO WORLD’,4,5)
LO WO

Reference Link 1

LIKE determines whether a specific character string matches a specified pattern.

Wildcard character Description Example
% Any string of zero or more characters. WHERE title LIKE ‘%computer%’ finds all book titles with the word ‘computer’ anywhere in the book title.
_ (underscore) Any single character. WHERE au_fname LIKE ‘_ean’ finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE ‘[C-P]arsen’ finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE au_lname LIKE ‘de[^l]%’ all author last names starting with de and where the following letter is not l.

Pattern Matching with the ESCAPE Clause
We can search for character strings that include one or more of the special wildcard characters.
To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided.
For example, a sample database contains a column named comment that contains the text 30%.
To search for any rows that contain the string 30% anywhere in the comment column, specify a WHERE clause such as:

WHERE comment LIKE ‘%30!%%’ ESCAPE ‘!’
If ESCAPE and the escape character are not specified, the Database Engine returns any rows with the string 30.
Or the other option is:

WHERE comment LIKE ‘%30[%]%’

LIKE Reference Link

Moving into advance comparison of LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX

This blog explains nicely the comparison of LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX.

When you need to search for a sub string at the beginning or end of data:

  • when performing a query against a table column, use CHARINDEX
  • when searching within a @varchar string variable, use LEFT/RIGHT
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s