August 25, 2014 at 1:42 am
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
-----------------------------------------------
79Responsible 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
August 25, 2014 at 2:25 am
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
August 25, 2014 at 4:55 am
tq very much
August 25, 2014 at 5:01 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply