We can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify.
Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.
Example of CHARINDEX:
USE AdventureWorks;
GO
SELECT CHARINDEX('ensure', DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
Examples of PATINDEX:
USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
Summary:
PATINDEX is CHARINDEX + WildCard Search. Use either of them depending your need.
Reference : Pinal Dave (http://blog.SQLAuthority.com) ,BOL – PATINDEX, BOL – CHARINDEX
pinaldave
CHARINDEX will only work on a text column if it can implicitly be converted to varchar (or ntext to nvarchar), so if the data is greater than 8000 bytes (approximately) you’ll get an error.
Hello Dave,
I have been using PATINDEX and CHARINDEX for my project. But I want to extend the functionality so that I can search a occurence of a ‘word’ in a ‘text’ field and a ‘varchar’ field. Is there any way this can be achieved with the same functions.
If anyone knows about how to handle the search, kindly mail me.
Regards
Keerthi
How to search for special characters and remove it from the string
actual string ‘Peninsula Inv00estment Management Comç’
after removed special char
‘Peninsula Inv00estment Management Com’
Pl give me a solution.
You can use the REPLACE() function to remove or replace special characters.
If you have a string like ‘Peninsula Inv00estment Management Comç’ that is in a field called CompanyName just type the following:
Select REPLACE(CompanyName,’ç’, ”) from Company. You can nest several REPLACE() functions to take out numerous characters at once.
Ron
Hi, I’ve just started using PatIndex – it’s proved useful in a lot of my work lately.
Right now I am trying to do a search for one string, OR another. In other words a kind of ’string count’ function. So if either substring exists in a value, it returns true, or a number.
The string is: where column like ‘%a%’ or column like ‘%b%’
The only problem is that ‘column’ is itself a very long-winded subquery, so I don’t want to have to write it out twice in the above string.
So can I use some kind of regex in patindex to find the above?
Hope this makes sense. thanks for your ideas..
i am using PatIndex and need more clear code… for example
i want no. of letters next to the symbol ‘-’ so i use
patindex(‘%-%’, company-name). its working successfully.
but if the word is of ‘company-name-id’ and i want to
consider the symbol which is appearing at the last of the
word, what i have to do? patindex(‘%-%’, company-name-
id)
if i use this i am getting same as the previous… any one plz
suggest me the correct solution for this?
Thanks! Your tips helped me clean and move data from one database to another :)
Nothing fancy, but was fun at least :D
Dave Coates
Port Elizabeth .NET Community Lead
South Africa
[...] SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX [...]
One bit of info I’m having trouble finding in any similar articles is… if PATINDEX is simply a more powerful CHARINDEX, why bother ever using CHARINDEX? Is there a performance difference?
@Ron
This is a similar question as to why use ‘=’ if we can always use ‘LIKE’. The major difference is if a wildcard needs to be escaped or not.
I would guess, but i have no idea, that pattern searching is slower because it does more.