[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query not functioning

Posted on 2007-08-06
4
Medium Priority
?
192 Views
Last Modified: 2013-11-05
I have a table with column DESCRIPTION that holds the following values
row 1 >>>>    2.5||~||3.0
row 2 >>>>    4.0||~||5.5
row 3 >>>>    5.5||~||6.5
row 4 >>>>    7.5||~||8.5
The datatype of the column is varchar(50)

I need a query to search the rows for a match.
eg: i wish to search of a value of '5.5' and it  should return 2 rows. ( ie row 2, row 3).


here is my query
SELECT * FROM KEYWORD_FARE_ZONE WHERE
PATINDEX(isnull(@SearchTerms,''), isnull(DESCRIPTION,'')) > 0


0
Comment
Question by:TECH_NET
  • 2
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
appari earned 1000 total points
ID: 19642704
try

SELECT * FROM KEYWORD_FARE_ZONE WHERE
PATINDEX('%' + isnull(@SearchTerms,'') + '%', isnull(DESCRIPTION,'')) > 0

0
 

Author Comment

by:TECH_NET
ID: 19642909
One more question if the value is 8.0 the query does not return any value.

I want to ensure the range also works.
0
 
LVL 39

Expert Comment

by:appari
ID: 19642965

do you mean if 8.0 it should return row 4?
if that is the case you have to write a function to divide the column values in to ranges and the compare the input value.
0
 

Author Comment

by:TECH_NET
ID: 19642972
Yes, that is what i need. Row 4 should be retrieved.

Can we have a SQL Function to do so?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question