SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help to using PATINDEX


Need help to using PATINDEX

Author
Message
Qira
Qira
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 734
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
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15434 Visits: 18620
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.
Cool

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

Qira
Qira
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 734
tq very much
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15434 Visits: 18620
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.
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search