Compare charindex and patindex

ArticleDirectory

• Compare charindex and patindex

Compare charindex and patindex Both the charindex and patindex functions return the starting position of the specified mode. Patindex can use wildcards, but charindex cannot. Both functions have two parameters: The mode in which you want to obtain the location. With patindex, the pattern is a literal string that can contain wildcards. Use charindex, which is a literal string (cannot contain wildcards ). String Value expression (usually a column name), in which Microsoft SQL Server searches for the specified mode. For example, in the search mode "wonderful ",TitlesTableNotesThe start position in a specific row of a column. Use pubs Select charindex ('Wonderful ', notes) From titles Where title_id = 'tc3218' The following is the result set: ---------------- 46 (1 row (s) affected) If no limit is imposed on the rows to be searched, the query returns all rows in the table, reports a non-zero value for all rows in this mode, and reports a zero value for other rows. For example, use the wildcard search mode "candies"CategoriesStart position of any row of the description column in the table: Use northwind Go Select categoryid, patindex ('% candies %', description) as position From categories Where patindex ('% candies %', description) <> 0 If no limit is imposed on the rows to be searched, the query returns all rows in the table and reports a non-zero value for all rows in this mode. Patindex pairTextData types are useful. Except is null, is not null, and like (these areTextIn addition to only other comparative operations that are valid for the type), patindex can also be used in the WHERE clause.