August 24, 2014 at 6:14 pm
My 2 table 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
I would like to do pattern matching in column - scopeOfWork.
So far, this is my T-SQL
declare @searchPattern nvarchar(200)
set @searchPattern='drawing, install'
SELECT
XJ.CVID
,XJ.company
,XJ.scopeOfWork, @searchPattern as searchPattern
,SIGN(ISNULL(CHARINDEX(CONVERT(NVARCHAR(200),XJ.scopeOfWork,1),@searchPattern,1),0)) AS isMatch
FROM dbo.u_Employment XJ
My present resultset was
CVIDcompanysearchPattern(No column name)isMatch
77Tatawa Industries Sdn BhdSupport Computer Hardware, So .....drawing, install0
77ICPT TechnologySupport Compputer Hardware, S .....drawing, install0
77Hock Hai PlazaControl Admin Department. Co .....drawing, install0
79Elektrisola (M) Sdn. BhdResponsible to complete 2 pro .....drawing, install0
79Giant HypermarketResponsible to put the price .....drawing, install0
78ACSON AIR-CONDITIONING & REFRIGERATIONTO SERVICE AND REPAIR AIR CON .....drawing, install0
78TUP ENTERPRISE SDN BHDTO CONTROL M&E WORK AT PROJEC .....drawing, install0
78PLA PERUNDING SDN BHDTO DRAW A M&E DRAWING .....drawing, install0
78HPM PERUNDING SDN BHDTO DRAW A AIR CONDITIONING DU .....drawing, install0
78HDM CARLOW SDN BHDTO DRAW A STAMP MECHINE ASSEM .....drawing, install0
78TONG TONG METAL SDN BHDTO DRAW A STRUCTUAL OF TRUSS .....drawing, install0
79Selangor Human Resource Development CentreThe Industrial Skills Enhance .....drawing, install0
At this level, my isMatch is inaccurate.
It's suppose to be find word - drawing, install then change isMatch=1
The Expected result as following,
CVIDcompanysearchPattern(No column name)isMatch
77Tatawa Industries Sdn BhdSupport Computer Hardware, So .....drawing, install0
77ICPT TechnologySupport Compputer Hardware, S .....drawing, install0
77Hock Hai PlazaControl Admin Department. Co .....drawing, install0
79Elektrisola (M) Sdn. BhdResponsible to complete 2 pro .....drawing, install1
79Giant HypermarketResponsible to put the price .....drawing, install0
78ACSON AIR-CONDITIONING & REFRIGERATIONTO SERVICE AND REPAIR AIR CON .....drawing, install0
78TUP ENTERPRISE SDN BHDTO CONTROL M&E WORK AT PROJEC .....drawing, install0
78PLA PERUNDING SDN BHDTO DRAW A M&E DRAWING .....drawing, install1
78HPM PERUNDING SDN BHDTO DRAW A AIR CONDITIONING DU .....drawing, install1
78HDM CARLOW SDN BHDTO DRAW A STAMP MECHINE ASSEM .....drawing, install1
78TONG TONG METAL SDN BHDTO DRAW A STRUCTUAL OF TRUSS .....drawing, install0
79Selangor Human Resource Development CentreThe Industrial Skills Enhance .....drawing, install0
If possible, please help
August 24, 2014 at 6:58 pm
Change the OR to AND if that is your criteria:
SELECT *, IsMatch=CASE WHEN PATINDEX('%drawing%', scopeOfWork) > 0 OR PATINDEX('%drawing%', scopeOfWork) > 0 THEN 1 ELSE 0 END
FROM [dbo].[u_Employment]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply