CHARINDEX: Get index of the delimiting space

CHARINDEX: Get index of the delimiting space

.Net Framework has very useful String.IndexOf that returns position of first occurrence of given char or substring in string. Although there is no function named IndexOf in T-SQL you can achieve same result with CHARINDEX and PATHINDEX function.

CHARINDEX function as counterpart of IndexOf()

CHARINDEX function has the following syntax:

CHARINDEX(expression1, expression2 [, start_location])

expression1 - sequence of characters to be found
expression2 - column or expression that is searched
start_location - optional parameter, start position of searching

Example that search for first position of word "chart" in ProductDescription column could look like:

SELECT CHARINDEX('chart', ProductDescription) FROM Products

PATINDEX function

PATINDEX function works very similar to CHARINDEX. Unlike CHARINDEX, PATINDEX function has only two parameters and first parameter is pattern that can include wildcard parameters. Syntax is like this:

PATINDEX('%pattern%', expression)

pattern - string that optionally can contain wildcard characters (%)

expression - column or expression to be searched

Example that solves the same problem as above would be:

SELECT PATINDEX('chart', ProductDescription) FROM Products

CHARINDEX vs. PATINDEX

So, there are two solutions that simulate C# or VB.NET IndexOf() function. The logical question is, which is better and what is the difference between them?

CHARINDEX has three parameters and it is better choice if you need to specify search starting position.

PATINDEX supports patterns with wild characters _ or %, or match range with [ ] and [^ ] so you have much more options to define what you want to find.

How to simulate LastIndexOf() in T-SQL?

You can simulate LastIndexOf with CHARINDEX or PATINDEX combined with REVERSE function. REVERSE function, as the name suggests, returns reverse of character sequence. So LastIndexOf implementation example that finds last position of word "chart" in column ProductDescription would be:

SELECT CHARINDEX('chart', REVERSE(ProductDescription)) FROM Products

Using of CHARINDEX or PATINDEX as substitute for LIKE keyword

One more use of CHARINDEX and PATINDEX functions is to find whether or not given string exists in column or expression. Instead of LIKE keyword, you can use example like this that returns all rows that contain word "chart" in ProductDescription column:

SELECT * FROM Products WHERE CHARINDEX('chart', ProductDescription) > 0



Example -

DECLARE @FullName VarChar(25), @SpaceIndex TinyInt SET @FullName = 'www.java2s.com' -- Get index of the delimiting space: SET @SpaceIndex = CHARINDEX('java', @FullName)
-- Return all characters to the left of the space: SELECT LEFT(@FullName, @SpaceIndex - 1) GO

Output

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

www.
(1 rows affected)

Comments

Post a Comment

Popular posts from this blog

Google to FTS Syntax Cheat Sheet

Image
OPERATOR EXAMPLE DESCRIPTION   nut Searches for inflectional forms of the word nut crank arm crank AND arm Searches for documents containing inflectional forms of the words crank AND arm crank and ann. The keyword AND is optional. tire OR air Searches for documents containing inflectional forms of the words tire or air, “reflector bracket” Performs a phrase search for the phrase "reflector bracket". hardware -bracket Searches for documents containing inflectional forms of the word hardware but not the word bracket. +clamp Searches for the word darn') without generating inflectional forms. ~seat Searches for thesaurus forms of the word seat Assemb* Searches for words that begin with the prefix assemb . <washer nut> Searches for documents that contain the words washer in close proximity to the word nut
Read more

@@rowcount

@@rowcount is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error you need to save it in a local variable if you want to use the value later, since @@rowcount is set after each statement. Since with SET you can only assign variable at a time, you must use SELECT if you need to save both @@error and @@rowcount into local variables: SELECT @err = @@error, @rowc = @@rowcount (For this reason, I prefer to always use SELECT for variable assignment, despite Microsoft's recommendations to use SET.) In T-SQL it is not an error if, for instance, an UPDATE statement did not affect any rows. But it can of course indicate an error in your application, as it could be an error if a SELECT returns more that one row. For these situations, you can check @@rowcount and raise an error and set a return value, if @@rowcount is not the expected value.
Read more

Sql Index

Image
Indexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
Read more