Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help to using PATINDEX Expand / Collapse
Author
Message
Posted Monday, August 25, 2014 1:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 10:02 AM
Points: 71, Visits: 600
My 2 tables and data as following,
CREATE TABLE [dbo].[u_CV](
[CVID] [int] NOT NULL,
[nme] [nvarchar](400) NULL,
CONSTRAINT [PK_u_CV] PRIMARY KEY CLUSTERED
(
[CVID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (68, N'Ahmad Jaezan Hj Ramly')
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (70, N'nadia bt abdul aziz')
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (71, N'SITI HAJAR BT MOHAMAD ARIS @ AZIZ')
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (73, N'Ng Kean Choong')
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (74, N'nur faizah binti mohamad khalid')
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (77, N'Lim Jinn An')
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (78, N'HAFIZAL BIN JAMALUDIN')
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (79, N'SITI AMINAH BINTI AWANG')
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (80, N'safinah md hashim')
INSERT [dbo].[u_CV] ([CVID], [nme]) VALUES (81, N'thiagarajan s/o karpayya')
/****** Object: Table [dbo].[u_Employment] Script Date: 08/25/2014 07:57:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[u_Employment](
[EMPLOYMENTID] [int] IDENTITY(1,1) NOT NULL,
[CVID] [int] NOT NULL,
[company] [nvarchar](200) NULL,
[position] [nvarchar](200) NULL,
[scopeOfWork] [text] NULL,
CONSTRAINT [PK_u_Employment] PRIMARY KEY CLUSTERED
(
[EMPLOYMENTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[u_Employment] ON
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (2, 77, N'Tatawa Industries Sdn Bhd', N'IT Executive', N'Support Computer Hardware, Software, Networking and Database.
Support ERP System.')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (3, 77, N'ICPT Technology', N'IT Executive', N'Support Compputer Hardware, Software, Networking and Database.
Support ERP System.')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (4, 77, N'Hock Hai Plaza', N'Admin Assistant Manager cum IT', N'Control Admin Department.
Control HR Department.
Control IT Department.
Control all the workers and facilities of Company.
Control and Apply the document with Gorvenment Department.')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (5, 79, N'Elektrisola (M) Sdn. Bhd', N'Practical Training', N'Responsible to complete 2 project.First,to install bare wire sensor at enamelling machine.After that I have to come out with the data analysis about the performance of enamelling machine before and after install the sensor. I also do a troubleshoot if occur the problem after install the sensor.Second project, do a research the best type of plate to use at enamelling machine to get a better product.')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (6, 79, N'Giant Hypermarket', N'Sales Assistant', N'Responsible to put the price label at goods and arrange at the rack. Beside that, attend the customer to buy the thing.')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (7, 78, N'ACSON AIR-CONDITIONING & REFRIGERATION', N'TECHNICIAN', N'TO SERVICE AND REPAIR AIR CONDITIONING AND REFRIGERATION')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (8, 78, N'TUP ENTERPRISE SDN BHD', N'SITE SUPERVISOR', N'TO CONTROL M&E WORK AT PROJECT SITE')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (9, 78, N'PLA PERUNDING SDN BHD', N'DRAUGHTPERSON', N'TO DRAW A M&E DRAWING')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (10, 78, N'HPM PERUNDING SDN BHD', N'DRAUGHTPERSON', N'TO DRAW A AIR CONDITIONING DUCTING AND ELECTRICAL DRAWING')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (11, 78, N'HDM CARLOW SDN BHD', N'DRAUGHTPERSON', N'TO DRAW A STAMP MECHINE ASSEMBLY DRAWING')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (12, 78, N'TONG TONG METAL SDN BHD', N'DRAUGHTPERSON', N'TO DRAW A STRUCTUAL OF TRUSS')
INSERT [dbo].[u_Employment] ([EMPLOYMENTID], [CVID], [company], [position], [scopeOfWork]) VALUES (13, 79, N'Selangor Human Resource Development Centre', N'Trainee (INSEP-Semiconductor Chip Design-Analog)', N'The Industrial Skills Enhancement Program (INSEP) is a unique graduate engineers training and development program that brings together the government, industries, local and foreign experts through SHRDC. From this program it will give me a chance to get the right competencies and innovative capabilities. INSEP aims to meet the current and future human resources challenges of the high technology industries in Malaysia. From this program I was exposing and learn more detail in semiconductor IC design. The module that was covered during this training are:

1.Bipolar Amplifier & OP-Amp Basic
2.Bipolar (Analog) Basic Design
3.Bipolar Layout
4.Fundamental LSI Test
5.MOS Analog circuit design
6.Project Management
7.Solid State Devices
8.UA741 circuit Analysis and Practice
9.Unix
10.Wafer Fabrication
11.Workstation and Cadence Tools
12. IC Testing and Reliability.
13.Assembly & Operation
14.Presentation Skill
15.Technical Report Writing
')
SET IDENTITY_INSERT [dbo].[u_Employment] OFF
/****** Object: ForeignKey [FK_u_Employment_u_CV] Script Date: 08/25/2014 07:57:52 ******/
ALTER TABLE [dbo].[u_Employment] WITH CHECK ADD CONSTRAINT [FK_u_Employment_u_CV] FOREIGN KEY([CVID])
REFERENCES [dbo].[u_CV] ([CVID])
GO
ALTER TABLE [dbo].[u_Employment] CHECK CONSTRAINT [FK_u_Employment_u_CV]
GO


Then, this is my Query
declare @searchPattern nvarchar(200)
set @searchPattern='drawing, install'

SELECT CVID, scopeOfWork,
IsMatch=CASE WHEN PATINDEX('%' + @searchPattern + '%', scopeOfWork)
> 0 OR PATINDEX('%drawing%', scopeOfWork) > 0 THEN 1 ELSE 0 END

FROM [dbo].[u_Employment]

Why my
CVID   | scopeOfWork   | isMatch
-----------------------------------------------
79 Responsible to complete 2 project.First,to install bare wire sensor at enamelling machine.After that I have to come out with the data analysis about the performance of enamelling machine before and after install the sensor. I also do a troubleshoot if occur the problem after install the sensor.Second project, do a research the best type of plate to use at enamelling machine to get a better product. 0

It'is suppose to be isMatch = 1

@searchPattern='drawing, install'

Wording install is contain in scopeOfWork.

My @searchPattern format is xxxx, xxxx, xxxx, xxxxx

Please help
Post #1606978
Posted Monday, August 25, 2014 2:25 AM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 2,532, Visits: 7,075
Here is a quick solution, uses charindex instead of patindex. It does leave some room for improvements though, especially in the search string splitting part.


USE tempdb;
GO

/* The strings to search for */
declare @searchPattern nvarchar(200) = N'drawing, install, ERP,Hardware';
/* Simple parsing, could/should be replaced by a better splitting function */
DECLARE @PATTERNS TABLE (SRC_PAT NVARCHAR(100) NOT NULL);
DECLARE @SQL_STR NVARCHAR(MAX) = N'SELECT LTRIM(SEARCH_PATTERN) AS SRC_PAT FROM (VALUES ('
+ NCHAR(39) + REPLACE(@searchPattern,N',',NCHAR(39)
+ N'),(' + NCHAR(39)) + NCHAR(39) + N')) AS X(SEARCH_PATTERN);';
INSERT INTO @PATTERNS(SRC_PAT)
EXEC (@SQL_STR);

;WITH PAT_MATCH AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY E.EMPLOYMENTID ORDER BY E.EMPLOYMENTID) AS E_RID
,E.EMPLOYMENTID
,E.CVID
,E.company
,E.position
,E.scopeOfWork
,SUM(CASE WHEN CHARINDEX(P.SRC_PAT,E.scopeOfWork,1) > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY E.EMPLOYMENTID) AS MATCH_COUNT
,SIGN(SUM(CASE WHEN CHARINDEX(P.SRC_PAT,E.scopeOfWork,1) > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY E.EMPLOYMENTID)) AS HAS_MATCH
FROM dbo.u_Employment E
OUTER APPLY @PATTERNS P
)

SELECT
PM.EMPLOYMENTID
,PM.CVID
,PM.company
,PM.position
,PM.scopeOfWork
,PM.HAS_MATCH
,PM.MATCH_COUNT
FROM PAT_MATCH PM
WHERE PM.E_RID = 1;


Partial Results
EMPLOYMENTID CVID        company                                      position                                           HAS_MATCH   MATCH_COUNT
------------ ----------- -------------------------------------------- -------------------------------------------------- ----------- -----------
2 77 Tatawa Industries Sdn Bhd IT Executive 1 2
3 77 ICPT Technology IT Executive 1 2
4 77 Hock Hai Plaza Admin Assistant Manager cum IT 0 0
5 79 Elektrisola (M) Sdn. Bhd Practical Training 1 1
6 79 Giant Hypermarket Sales Assistant 0 0
7 78 ACSON AIR-CONDITIONING & REFRIGERATION TECHNICIAN 0 0
8 78 TUP ENTERPRISE SDN BHD SITE SUPERVISOR 0 0
9 78 PLA PERUNDING SDN BHD DRAUGHTPERSON 1 1
10 78 HPM PERUNDING SDN BHD DRAUGHTPERSON 1 1
11 78 HDM CARLOW SDN BHD DRAUGHTPERSON 1 1
12 78 TONG TONG METAL SDN BHD DRAUGHTPERSON 0 0
13 79 Selangor Human Resource Development Centre Trainee (INSEP-Semiconductor Chip Design-Analog) 0 0
Post #1606983
Posted Monday, August 25, 2014 4:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 10:02 AM
Points: 71, Visits: 600
tq very much
Post #1607013
Posted Monday, August 25, 2014 5:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 2,532, Visits: 7,075
Qira (8/25/2014)
tq very much


You are welcome.
While the splitting part works fine with very short list of elements, you might want to look at DelimitedSplit8K for better splitting performance when handling larger element lists.
Post #1607014
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse