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 (https://blog.sqlauthority.com) ,BOL – PATINDEX, BOL – CHARINDEX
57 Comments. Leave new
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.
Please elleborate it Alex. I tried it with an entry of more than 24000 characters. Still it works fine.
Hi Gupta,
It wont work based on Characters. Alex said it’ll throw error when the data exceeds 8000 bytes. Check the size not the number of characters.
Sukriya…
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
Refer this post. This will search for a value in all character columns of all the tables and and return column name , table name
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.
declare @string varchar(100)
declare @string1 varchar(100)
set @string = ‘Peninsula Inv00estment Management Comçagement Comç0estment’
set @string1 = replace(@string, char(231), ”)
select rtrim(@string1)
the output is
Peninsula Inv00estment Management Comagement Com0estment
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..
“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.”
You can move your query down to FROM clause (inline view), a then filter it with outer where clause.
E.g. if your query is:
select … from table
where (big expression) like something
or (big expression) like something
it can be written as:
select * from — * is ok – columns are defined in inline view
(
select … ,
bige = (big expression) — just once
from table
) t
where bige like something
or bige like something
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?
Can you post some more sample data with expected result?
Did anyone ever answer your question about searching for the location of the second – ? I am also trying to do this
Refer this example
declare @s varchar(100)
set @s=’company-name-id’
set @s=substring(@s,patindex(‘%-%’, @s)+1,len(@s))
set @s=substring(@s,patindex(‘%-%’, @s)+1,len(@s))
select @s
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
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?
There may a slight performance difference. Note that patidex is desinged to work in unicode data too
@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.
see the diff’
@Name=Mari & Manikandan
select CHARINDEX(‘ & ‘,@Name)
select PATINDEX(‘%[^ ]&[^ ]%’,@Name)
sorry,
select CHARINDEX(‘&’,’Mari&Mani’)
select PATINDEX(‘%[^ ]&[^ ]%’,’Mari&Mani’)
watch this,
select CHARINDEX(‘[^ ]&[^ ]’,’Mari&Mani’)
select PATINDEX(‘%[^ ]&[^ ]%’,’Mari&Mani’)
How do I find Number in string in Mysql.
All i need is the position of the number.
a number can be anything between 0-9
In patindex we can do:
Select patindex(‘%[0-9]%’, columname)
But dunno how to find number in my string using any function in Mysql
If you are using mysql, you should use locate function instead of patindex
Hi,
i have the column name empID in multiple tables in my database(sql server 2005)
I have to find the total nuber of occurance of empID in the whole database (count the records (data) of all tables which contains the empID)
how to write the query to achieve the above.
Thanks in advance,
Nikhil.
As a suggestion, you need to work on
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name = ’empid’
ORDER BY schema_name, table_name;
you can use following query for sql 2005:
select * from sysobjects where id in (select id from syscolumns where name =’empid’)
Do you need to know number of tables that has empid or tables that has particular empid value?
How about another idea? I’m also curious how would doing a CHARINDEX on a field in a record to find 2 characters perform in comparison to using dynamic sql to pull a string out of that same field to do use the IN function?
DECLARE @sql varchar(2000)
SELECT @sql =
(
SELECT
‘(”’ + c1 + ”’)’
FROM
t_control (NOLOCK)
WHERE
control_type = ‘OVERRIDE’
)
SET @sql = ‘SELECT COUNT(*) WHERE ”XX” IN ‘ + ” + @sql + ”
SELECT @sql
EXEC (@sql)
There is a chance that dynamic sql may outperform charidex if the XX is a indexed column
I have a text type column in one of my tables that will contain large amounts of text….25,000 to 40,000 characters. When I try to use charindex(‘fcCount’,data) where data is the column containing the text, it will always return zero, even though, when I look the data, the ‘fcCount’ is actually there. This is a SQL 2000 database that we plan to upgrade this year, but need to get this working in the interrum. Any help would be appriciated.
Did you get any calues when you try this?
select columns from table
where data like ‘%fcCount%’
Hello All,
I have a table that contains a field where I store several items, and one of them is an IP address, enclosed in pipes.
Example:
DEVICE IP: |192.168.2.90| Puerto: 1117 01234567890 14142422 Resultado Positivo!
The IP of course is different in lenght for each record; I am using this function:
SELECT id, SUBSTRING(transaccion, CHARINDEX(‘|’, transaccion)+1,15) AS ip FROM Mydatabase.dbo.Mytable
But it is extracting another string with a constant lenght.
Is there a way to extract the IP, using a function letting know SQL that the string is enclosed by Pipes?
Thank you all!
This is generalised method
select replace(parsename(replace(replace(data,’.’,’*’),’|’,’.’),2),’*’,’.’) from
(
select ‘DEVICE IP: |192.168.2.90| Puerto: 1117 01234567890 14142422 Resultado Positivo!’ as data
) as t
You can refer these to get more indeas
Hi,
I usually get a data from our clients in SQL. I need to import it in a software which can import it only in csv format. If there is a comma in any field in SQL (by error) it makes it as a separate column while exporting. Then the software throws an error. Hence before exporting it to a csv file, I want to know, if there is any comma in any of the fields. I know the query
where column_name like ‘%,%’
But the problem is, there are more than 60 columns to be checked. It is very time consuming. So is there is any other simple way to search it in Database as a whole (rather than columnwise).
Regards,
Tejas
Hi Tejas,
You should export data to CSV with text qualifier like “. So your data looks like: “Tejas, Shah”,”AAAA”,”BBB” etc..
So when software tries to import it, it will not consider “,” between qualifier.
Let me know if it helps you.
Thanks,
Tejas Shah
SQLYoga.com
Hi Tejas,
Thanks for your response. I will do it tomorrow and will let you know.
Thanks,
Tejas
Hi Tejas Shah,
Software considers only ‘Comma’. So its not working. I think, i need to check individually.
So my option is export it to excel, Ctrl+F, check the fields where commas exist, then replace in sql. Still, its too time consuming. Thanks for ur suggestion. I can use it for some other softwares.
Regards,
Tejas
Hi All
I have a requirement to change a column’s value in a table carrying 16 digit credit card number(Column name is ‘SCREDITCARDNO’ and data type is varchar(50),table name is ‘ContactDetails’) into—->
First 6 digit of credit card number, followed by ****** (6 starts) and then followed by last 4 digit of the credit card number.
It’s SQL Srver 2005.
Please help me with this.
First do the SELECT
SELECT left(SCREDITCARDNO,6)+’******’+right(SCREDITCARDNO,4) FROM ContactDetails