January 14, 2004 at 7:09 pm
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
January 14, 2004 at 7:20 pm
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
January 14, 2004 at 7:26 pm
Yes,i did still it does not work.
with out % 1st query runs but why not 2nd one?
January 14, 2004 at 7:29 pm
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
January 14, 2004 at 7:33 pm
Yes that is it. Look at "Pattern Matching in Search Conditions" in Books Online.
--------------------
Colt 45 - the original point and click interface
January 14, 2004 at 7:52 pm
Thanks guys i'm trying..
January 14, 2004 at 8:06 pm
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.
January 14, 2004 at 8:11 pm
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]'
January 14, 2004 at 8:17 pm
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
January 14, 2004 at 8:27 pm
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
January 14, 2004 at 8:36 pm
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
January 14, 2004 at 8:43 pm
Dear Phill Carter
you are 100% right.yes i am using it in stored proc.
I'll implement your suggestion.
Thank you so much
January 15, 2004 at 5:59 pm
Dear Phill Carter
i implemented it and its working fine .
Thank you very much
January 15, 2004 at 6:04 pm
Glad to be of service.
--------------------
Colt 45 - the original point and click interface
January 16, 2004 at 12:55 pm
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply