Need help to write SQL Query to pull data

Question

Need help to write SQL Query to pull data

tapinderaujla

SSC Veteran

Points: 279
More actions
April 13, 2018 at 12:54 pm

#403261

Hi,
I need help to write a SQL query to pull data as per where clause given below.
I have database table that store the "File #" and "File Title" in single column of "Varchar" type. I need to pull the the rows that matches following clause:
------------------------------------------------------------------
Where FileName in ('F007215','F001375','1234563','1734566')
------------------------------------------------------------------
The table structure and data is given below:
------------------------------------------------------------------
CREATE TABLE [dbo].[TempData](
[FileName] [varchar](275) NULL
) ON [PRIMARY]
INSERT INTO TempData ([FileName]) VALUES ('F007215 - Fellowships Rejected and Ineligible')
INSERT INTO TempData ([FileName]) VALUES ('F002196 - Postgraguate Rejected')
INSERT INTO TempData ([FileName]) VALUES ('001517 - Postgraduate Fellowships On Hold')
INSERT INTO TempData ([FileName]) VALUES ('Conference 2004-2012 - F001375')
INSERT INTO TempData ([FileName]) VALUES ('test March - 1734566')
INSERT INTO TempData ([FileName]) VALUES ('F007093 - SAujla Inc.')
INSERT INTO TempData ([FileName]) VALUES ('1234563')
------------------------------------------------------------------
Thanks,
Tapinder

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

tapinderaujla - Friday, April 13, 2018 12:53 PM
Hi,
I need help to write a SQL query to pull data as per where clause given below.
I have database table that store the "File #" and "File Title" in single column of "Varchar" type. I need to pull the the rows that matches following clause:
------------------------------------------------------------------
Where FileName in ('F007215','F001375','1234563','1734566')
------------------------------------------------------------------
The table structure and data is given below:
------------------------------------------------------------------
CREATE TABLE [dbo].[TempData](
[FileName] [varchar](275) NULL
) ON [PRIMARY]
INSERT INTO TempData ([FileName]) VALUES ('F007215 - Fellowships Rejected and Ineligible')
INSERT INTO TempData ([FileName]) VALUES ('F002196 - Postgraguate Rejected')
INSERT INTO TempData ([FileName]) VALUES ('001517 - Postgraduate Fellowships On Hold')
INSERT INTO TempData ([FileName]) VALUES ('Conference 2004-2012 - F001375')
INSERT INTO TempData ([FileName]) VALUES ('test March - 1734566')
INSERT INTO TempData ([FileName]) VALUES ('F007093 - SAujla Inc.')
INSERT INTO TempData ([FileName]) VALUES ('1234563')
------------------------------------------------------------------
Thanks,
Tapinder

So, you want to include all rows that contain the values in your WHERE clause? You would have 2 options depending on the amount of values expected.
First you could convert the IN into multiple conditions:
WHERE FileName LIKE '%F007215%' OR FileName LIKE '%F001375%' OR FileName LIKE '%1234563%' OR FileName LIKE '%1734566%'
Or to build a table to store all the values and keep a single condition.
WHERE EXISTS (SELECT 1 FROM ValuesTable vt WHERE FileName LIKE '%'+ vt.Value + '%'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tapinderaujla SSC Veteran Points: 279 More actions · Answer 2

Luis Cazares - Friday, April 13, 2018 1:25 PM
tapinderaujla - Friday, April 13, 2018 12:53 PM
Hi,
I need help to write a SQL query to pull data as per where clause given below.
I have database table that store the "File #" and "File Title" in single column of "Varchar" type. I need to pull the the rows that matches following clause:
------------------------------------------------------------------
Where FileName in ('F007215','F001375','1234563','1734566')
------------------------------------------------------------------
The table structure and data is given below:
------------------------------------------------------------------
CREATE TABLE [dbo].[TempData](
[FileName] [varchar](275) NULL
) ON [PRIMARY]
INSERT INTO TempData ([FileName]) VALUES ('F007215 - Fellowships Rejected and Ineligible')
INSERT INTO TempData ([FileName]) VALUES ('F002196 - Postgraguate Rejected')
INSERT INTO TempData ([FileName]) VALUES ('001517 - Postgraduate Fellowships On Hold')
INSERT INTO TempData ([FileName]) VALUES ('Conference 2004-2012 - F001375')
INSERT INTO TempData ([FileName]) VALUES ('test March - 1734566')
INSERT INTO TempData ([FileName]) VALUES ('F007093 - SAujla Inc.')
INSERT INTO TempData ([FileName]) VALUES ('1234563')
------------------------------------------------------------------
Thanks,
Tapinder
So, you want to include all rows that contain the values in your WHERE clause? You would have 2 options depending on the amount of values expected.
First you could convert the IN into multiple conditions:
WHERE FileName LIKE '%F007215%' OR FileName LIKE '%F001375%' OR FileName LIKE '%1234563%' OR FileName LIKE '%1734566%'
Or to build a table to store all the values and keep a single condition.
WHERE EXISTS (SELECT 1 FROM ValuesTable vt WHERE FileName LIKE '%'+ vt.Value + '%'

🙂
Thanks Luis!

Your second solution worked for me. As there are more than 2 millions rows in this table, and the sql query (as per your suggestion) is taking too much time to execute. What are the best options available to decrease the execution time without touching the database structure .

Tapinder

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

tapinderaujla - Friday, April 13, 2018 1:44 PM
🙂
Thanks Luis!
Your second solution worked for me. As there are more than 2 millions rows in this table, and the sql query (as per your suggestion) is taking too much time to execute. What are the best options available to decrease the execution time without touching the database structure .
Tapinder

There's no way to improve this as you have a leading wildcard and it would prevent any index seek.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2