Skip to main content

PATINDEX FUNCTION IN SQL SERVER

Returns the starting position of first occurrence of pattern in a specified expression.

Syntax:

Select Patindex(pattern char,expression char) returns int

Note:

  • It returns the integer value.
  • It returns the zero (0) when you are specified position is not matched in the expression.


Here is the simple string with postions for using entire examples in the Patindex Function.

1 2 3 4 5 6 7 8 9 10 11 12

L E A D F I R S T S Q L


Example-1:
SELECT PATINDEX('%a%','LEADFIRSTSQL') [PATINDEX_POSITION]







Example-2:
SELECT PATINDEX('%S%','LEADFIRSTSQL') [PATINDEX_POSITION]






Example-3:
SELECT PATINDEX('%_R_%','LEADFIRSTSQL') [PATINDEX_POSITION]







Example-4:
SELECT PATINDEX('%[abcde]%','LEADFIRSTSQL') [PATINDEX_POSITION]






Here is the example any of the character postion starting in the expression. It would match either a,b,c,d,e in the expression.

Example-5:
SELECT PATINDEX('%[7]%','LEADFIRSTSQL') [PATINDEX_POSITION]






Here Position is the Zero ,because of the 7 numberic value not exists the specified expression. 


Example-6:

SELECT PATINDEX('%[^A-Z]%','LEADFIRSTSQL1') [PATINDEX_POSITION]





 Here not match any character in [A-Z] regular expression . 
Labels:

Comments

Post a Comment

Popular posts from this blog

SP_MSForeachtable

Some times we need to query on the all the tables in one data base single statement. We use SP_MSForeachtable this is known as undocumented stored procedures . These all are system stored procedures. These stored procedures is place in Master database. NOTE: Please do not run all these queries in Production environment Example: create database UnDocumentedStoredProcedure use UnDocumentedStoredProcedure create table Emp ( ID int identity ( 1 , 1 ), Name varchar ( 50 ), Salary int ) insert into Emp ( Name , Salary ) values ( 'rakesh' , 8000 ),( 'raju' , 9000 ) create table Dept ( ID int identity ( 1 , 1 ), DeptName varchar ( 100 ) ) insert into Dept ( DeptName ) values ( 'CSE' ),( 'IT' ) We are created new database and also created some table with some dummy data. Select all tables data: exec sp_MSForeachtable 'select * from ?...
1 comment
Read more

SET ROWCOUNT

Stop the processing after the number of rows has been affected. Example: Step-1: Create the Table and insert some sample data. CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' In above Insert Statement we inserted 5 sample records. Step-2: Using SET ROWCOUT SET ROWCOUNT 3 ; SELECT * FROM STUDENT ORDER BY ID DESC It will affected 3 rows after that processing has been stopped. Step-2: Using SET ROWCOUT 0 SET ROWCOUNT 0 ; SELECT * FROM STUDENT ORDER BY ID DESC ...
7 comments
Read more