Compare single value with comma seperated column

Rajarajan

11y1.1kSQL

I have two tables - tblpatients and tbldoctor, Both table have unique fields namely patient_id and doctor_id, Here a single patient can get treatment from more than one doctor, So I am storing the doctor_id data from tbldoctor table to tblpatients table, For eg 2,3

Now I need to compare doctor_id field in both the table to get the details of patient for whom doctor given treatment, like doctor_id=doctor_id

But As I am stroing doctor_id field in tblpatients as comma separated, I am not getting any result

Please give a solution to achieve this

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Venkat Kumar11y ago
Its working in my machine. Sure it will work.

Run the below script
USE [MyDb]
GO
/****** Object: Table [dbo].[tblpatient] Script Date: 04/22/2015 17:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblpatient](
[ptname] [varchar](100) NOT NULL,
[docter_id] [varchar](100) NOT NULL,
[id] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

INSERT [dbo].[tblpatient] ([ptname], [docter_id], [id]) VALUES ('vinay', '1,2', 1)
/****** Object: Table [dbo].[tbldoctor] Script Date: 04/22/2015 17:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbldoctor](
[docter_id] [int] NOT NULL,
[Doctorname] [varchar](100) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tbldoctor] ([docter_id], [Doctorname]) VALUES (1, 'venkat')
INSERT [dbo].[tbldoctor] ([docter_id], [Doctorname]) VALUES (2, 'manish')
INSERT [dbo].[tbldoctor] ([docter_id], [Doctorname]) VALUES (3, 'siva')

Run the below query

WITH tblpatientdemo AS
(
SELECT A.[ptname],
Split.a.value('.', 'VARCHAR(100)') AS docter_id
FROM (SELECT [ptname],
CAST ('' + REPLACE([docter_id], ',', '') + '' AS XML) AS String
FROM tblpatient) AS A CROSS APPLY String.nodes ('/M') AS Split(a)
),

tbldoctordemo as
(
select docter_id, Doctorname from tbldoctor
)
select * from tblpatientdemo join tbldoctordemo on tblpatientdemo.docter_id=tbldoctordemo.docter_id
Rajarajan11y ago
Hi,
I created the table and run the query, below error is displayed
Msg 1934, Level 16, State 1, Line 1
SELECT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Venkat Kumar11y ago
hai please check this.. This is for single table only. use this and join two tables.

USE [MyDb]
GO
/****** Object: Table [dbo].[tblcity] Script Date: 04/22/2015 17:22:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblcity](
[id] [int] NOT NULL,
[name] [varchar](100) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tblcity] ([id], [name]) VALUES (1, 'Hyd,Bng')
INSERT [dbo].[tblcity] ([id], [name]) VALUES (2, 'Vja,Pune')

Below is the query

SELECT A.[id],
Split.a.value('.', 'VARCHAR(100)') AS String
FROM (SELECT [id],
CAST ('' + REPLACE([name], ',', '') + '' AS XML) AS String
FROM tblcity) AS A CROSS APPLY String.nodes ('/M') AS Split(a);