interesting query

Question

Post reply

interesting query

sqlpro

Hall of Fame

Points: 3859
More actions
January 14, 2004 at 7:09 pm

#177833

Hi friends
I have a interesting situation here.
I'm currently running sql 2000 and i have table like following
tablename :tab1
flields :fld1 varchar(5),fld2 varchar(2000)
i have data like following
'!6mTK','checked in'
'Ph[(L','Family Practice Unit University of Adela'
when i run following query it works fine
select * from tab1 where fld1 like '!6mTK'
but it does not return any data when i say
select * from tab1 where fld1 like 'Ph[(L'
but it works when i say
select * from tab1 where fld1 = 'Ph[(L'
is it something to do with keywords in the data?if so what
is work around?
I need to use "Like" condition not "=" bcoz its dynamic query.
Any ideas please
Thank you very much

Viewing 15 posts - 1 through 15 (of 15 total)

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

When you use the LIKE operator you need to include a wildcard character in the criteria.

EG: select * from tab1 where fld1 like 'Ph[(L%'

This will return all rows where fld1 begins with Ph[(L

--------------------
Colt 45 - the original point and click interface

sqlpro Hall of Fame Points: 3859 More actions · Answer 2

Yes,i did still it does not work.

with out % 1st query runs but why not 2nd one?

justevolved SSC Enthusiast Points: 188 More actions · Answer 3

While using wild chaaracters one needs to use the escape characters while using symbols like % and [. Look in BOL for the syntax for I am not recollecting it right now.

You have to dig for tons of dirt to get an ounce of Gold

philcart SSC-Forever Points: 47795 More actions · Answer 4

Yes that is it. Look at "Pattern Matching in Search Conditions" in Books Online.

--------------------
Colt 45 - the original point and click interface

sqlpro Hall of Fame Points: 3859 More actions · Answer 5

sqlpro

Hall of Fame

Points: 3859

January 14, 2004 at 7:52 pm

#490121

Thanks guys i'm trying..

sqlpro Hall of Fame Points: 3859 More actions · Answer 6

i found following books online

If the LIKE '5%' symbol is specified, SQL Server searches for the number 5 followed by any string of zero or more characters.

For example, this query shows all system tables in a database, because they all begin with the letters sys:

SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME LIKE 'sys%'

To see all objects that are not system tables, use NOT LIKE 'sys%'. If you have a total of 32 objects and LIKE finds 13 names that match the pattern, NOT LIKE finds the 19 objects that do not match the LIKE pattern.

You may not always find the same names with a pattern such as LIKE '[^s][^y][^s]%'. Instead of 19 names, you may get only 14, with all the names that begin with s or have y as the second letter or have s as the third letter eliminated from the results, as well as the system table names. This is because match strings with negative wildcards are evaluated in steps, one wildcard at a time. If the match fails at any point in the evaluation, it is eliminated.

sqlpro Hall of Fame Points: 3859 More actions · Answer 7

i tried again like following but still did not work

select * from tab1where fld1 like 'Ph[(L' escape '['

select * from tab1where fld1 like like '[Ph[(L]'

philcart SSC-Forever Points: 47795 More actions · Answer 8

Try

select * from tab1where fld1 like like 'Ph[[](L'

Note the use of [] to enclose the wildcard character in the data.

--------------------
Colt 45 - the original point and click interface

sqlpro Hall of Fame Points: 3859 More actions · Answer 9

hi Phill Carter

it worked like a charm

but how can use it in my dynamic query

some times there may be [ char in my data so how frame my query .for example see my possible data in my initial post.

Thank you very much

philcart SSC-Forever Points: 47795 More actions · Answer 10

I'm presuming this is running via a stored procedure and you pass in the criteria using a parameter.

That being the case you would need to validate the passed parameter before executing the query.

Use the REPLACE function to change [ into [[] and % into [%]

EG:

CREATE PROCEDURE List @fld varchar(10)
AS

SET @fld = REPLACE(@fld,'[','[[]'
SET @fld = REPLACE(@fld,'%','[%]'

select * from tab1 where fld1 like @fld

--------------------
Colt 45 - the original point and click interface

sqlpro Hall of Fame Points: 3859 More actions · Answer 11

Dear Phill Carter

you are 100% right.yes i am using it in stored proc.

I'll implement your suggestion.

Thank you so much

sqlpro Hall of Fame Points: 3859 More actions · Answer 12

Dear Phill Carter

i implemented it and its working fine .

Thank you very much

philcart SSC-Forever Points: 47795 More actions · Answer 13

Glad to be of service.

--------------------
Colt 45 - the original point and click interface

DanaH1976 Ten Centuries Points: 1046 More actions · Answer 14

If you're using dynamic SQL, you could substitute CHAR(39) for the single quote, CHAR(91) for the left bracket and CHAR(93) for the right bracket, as follows:

------------------------------------------------------

DECLARE @sql varchar(200)

SELECT @sql = 'SELECT * FROM tab1 WHERE fld1 LIKE ' + CHAR(39) + 'Ph' + CHAR(91) + '(' + 'L' + CHAR(39)

EXEC(@SQL)

------------------------------------------------------

It will generate the following: 'SELECT * FROM tab1 WHERE fld1 LIKE 'Ph[(L'

I prefer using these ASCII characters because it's easier to separate search characters from wildcard characters.

Dana
Connecticut, USA
Dana