Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Wildcard In The WHERE Clause


I'm trying to pull some data from a table with the option to filter on 2 columns.

I've set up my sql statement to accept 2 parameters and I'd like to be able to send 1 or 2 wildcards if needed. My statement looks like this:SELECT *
FROM City WHERE CityName= @CityName AND State= @State

For example, if you wanted all of the cities in all of the states I would pass (*,*) as parameters.

Or if I wanted to see all of the states that have a city named Richmond, I'd pass (Richmond,*) as the parameters.

The wildcards are not returning anything and I don't know why. It works fine if I pass something like (Indianapolis,Indiana) as parameters so I think it's in my use of the wildcards that is wrong.


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Wildcard Used For SQL IN Clause
I am trying to control how users view records.  I want to create a solution that would, for instance permit:user A to view Store 1user B to view store 2 and store 3user C to view store 5 and store 6and User D to view all stores even if more stores were added in the future (in other words user D would have access to all records)
I want to create an 'authorization table' so that different users can see different records.  I think the easiest way to do this is to pass a parameter to the where clause, but the problem that I face is the how can I use a wildcard in a SQL 'IN' clause?  Does anyone have any suggestions.  Perhaps I am taking the wrong approach.  I would appreciate any guidance.

View Replies !   View Related
Trouble Getting SQL Query To Work Using LIKE Clause With Astersik (*) As Wildcard In
Please help.

Has anyone seen a problem querying Excel or Access database when the "LIKE" clause is used with the "*" wildcard? The problem I'm seeing is that the query fails to return a dataset whenever "*" wildcard is used.

For Example,

sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" 'query WORKS

sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA*'" 'query does NOT WORK

Assume that Table name is "EmployeeData", with "ID", "Name" and "Birthdate" as Fields.





The code I use is listed as follows:

Imports System.Data.OleDb

Imports System.Data
Public Class Form1

Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:ExcelData1.xls;" & _

"Extended Properties=""Excel 8.0;HDR=YES"""

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

'Use a DataSet to Query data from the EmployeeData table.

Dim QryConn As New OleDbConnection(m_sConn1)

'Dim strQry as string="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" '- works

Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AA*'" ' does notwork

Dim da As New OleDbDataAdapter(strQry, QryConn)

Dim ds As DataSet = New DataSet


Dim dr As DataRow

For Each dr In ds.Tables(0).Rows 'Show results in output window

Debug.WriteLine(dr("Id") & ", " & dr("Name") & ", " & dr("Birthdate"))

'Expected output:




End Sub

End Class

View Replies !   View Related
hi,i have an sqldatasource a gridview and dropdownlistthe gridview is updated on the selectedIndexChanged Event of the dropdownlistmy goal is to add an item in the dropdownlist with the name ALLwhich should matches all the records in the databasei tried to put the value of the all item = * and % but neither seems to workany help on what could be going wrong would be appreciated

View Replies !   View Related
Wildcard Use
Hello everyone and thanks for your help in advance.  I am working on an application that does a property search off of a database that contains approximately 40000 records.  The search criteria allows the use to specify a minimum and maximum price, subdivision name, number of bedrooms, etc.  I set up a stored procedure to query the databse.  if one of the parameters is not specified, i simply pass it a "%".  However, when i execute this sproc in Query Analyzer, it takes in excess of 10 seconds to return the records, even  if only one or two are returned.  I am assuming this is due to the use of the wildcard character when the user does not have a preference, but I am not sure of any other way to do this.  Any help on this topic would be grealy appreciated.

View Replies !   View Related
CONTAINS And Wildcard
Is it a good idea to have multiple contains? I have this query:Select * from myTable where contains (Col1, 'Africa') or (Col2, 'Africa')Also, I tried this, didn't return anything:Select * from myTable where contains (Col1, 'Africa*') or (Col2, 'Africa')Both Col1 and Col2 has the string 'Africa' and 'African' in it.--sharif

View Replies !   View Related
Looking For A Wildcard

how do i contsruct a SQL query to search for the % character in a data field?


View Replies !   View Related
Using Wildcard

alter procedure sp_ADsearch @tbname varchar(1000),@searchproduct nvarchar(500)


declare @sqlstr nvarchar(1000)

set @sqlstr='select * from '

set @sqlstr= @sqlstr + @tbname

set @sqlstr= @sqlstr + ' where name like '

set @sqlstr= @sqlstr + '%' + @searchproduct + '%'

exec sp_executesql @sqlstr


exec sp_ADsearch product,ee

when executing the procedure i am getting an error "Incorrect syntax near 'ee'.".

whats wrong with the syntax? how to use % along with variables

View Replies !   View Related
Is There A Way To Use Wildcard Characters...
to reference columns.
i have a table that has a column that is repeated 25 times, sort of.

tbl_ID (PK)
tbl_c_1 char 5
tbl_c_2 char 5
tbl_c_3 char 5
tbl_c_4 char 5
tbl_c_5 char 5
tbl_c_25 char 5

everytime i want to query for any of tbl_c_? that contain a specific value i have to reference all 25 in my query. is there a better way?
I cannot change the table.

View Replies !   View Related
Can someone tell me if you can use a wildcard using sp_change_users_login? I tried the '%' character in the 'Auto_Fix' option but it doesn't work. It looks like 'Auto_Fix' only allows one user update at a time. The BOL doesn't say whether you can use wildcard. HELP!!

To Sekhar - Thanks for your reply. I also saw that workaround in MS site.

View Replies !   View Related
Wildcard For Integers Or Something?
I'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.

Here's what I've got:
and b.user_ID = CASE @user_ID WHEN 0 THEN '%'
ELSE @user_ID
...the problem being the '%' part. That won't work on an integer column.

Does anyone have any ideas here?


View Replies !   View Related
Wildcard Misuse?
I've got a text field with a list of dates in this format:


The field can contain multiple dates (as listed above). I need to query the db and retrieve dates by month and year. For example, all fields containing 10/%/06 or 11/%/%06.

My query:

SELECT * FROM tblCalData WHERE visible='1' and approved='1' and eventDates LIKE '11%06%' ORDER BY eventDates DESC

so even though a field contains dates in October and November, selecting all November dates, in this example, don't appear. The query seems to be looking only at the first date.

Is this possible? Is there another/better way to do this?



View Replies !   View Related
Wildcard In Query?
I want to update data only where the value of the 'image_path' column is NOT =*

Im basically trying to exclude creating duplicates, where this path already exists.

Here is my code:

FROM GRAB where Col066 <> ' '
update IMAGE
FROM IMAGE WHERE image_name2 IS NOT NULL and perimage_path is NOT =*

What is the proper code to do this. I know the last line does not work. Thanks

View Replies !   View Related
Wildcard In Query?
I screwed up my database and double added data to a text field.

For example I need made this:

'' +PATH+ '.jpg'

Look like this:


How can I query to reset this?

update perimage
set PERIMAGE_PATH = '' +PATH+ '.jpg'

That just makes the problem worse

View Replies !   View Related
WildCard If Not Value Selected
HI Guys, I have a question.
I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).

Current Statement:
And tblRetailer_Contact.faxcontact LIKE *

I substituted:
And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’

This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?

DECLARE @FaxContact as varchar (50)

SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5)
SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5)
SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)

SELECT tblEData.Timestamp As [TimeStamp],
LTRIM(RTrim([ResultsCustName])) AS CustName,
LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status],
FaxContact AS FaxContact,
ResultsPKey As ResultsKey
INTO tmpE_Callbacks
FROM tblEData
LEFT JOIN tblContact
ON tblEData.RetailerPrefix = tblContact.Prefix
WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date
AND FaxContact Like '%@FaxContact%'

Thanx so much,

View Replies !   View Related
Wildcard Searches
I have a number of functions that require the input of parameters in order to ultimatly create a report under Reporting Services by making useof a Stored Procedure.

All the functions etc work as does the stored procedure, but it only works if I specify data that I know exists e.g.

DECLARE @return_value int

EXEC @return_value = [dbo].[spWTRalldatareportsummary]

@dt_src_date = N'04/28/2006',

@chr_div = N'NE',

@vch_portfolio_no = 3,

@vch_prop_cat = N'core'

SELECT 'Return Value' = @return_value


How can I set this so that it will wild card the value. For example rather than having to specify

@chr_div = N'NE', I could specify something like

@chr_div = N *, so it would show both NE and SW values in the result set.

Anybody point me in a direction here. I have tried % but that does not seem to work, I get a

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near '%'.

Thank in Advance

View Replies !   View Related
Wildcard Ssl Certificates?
Does SQL Server support wildcard Certificates. When you install the wild cert in the certificate store, the sql configuration manager does not see it in its drop down list. Id it does, what are the steps or please point me to the right direction. Does the cert need to be specifically for that particular hostname. Thanks

View Replies !   View Related
Using Wildcard With Parameters

The where statement on my stored proc is as follows:

Where actv.ProjID + '-' + actv.Activity = @project

@project is something the user provides in the form of "xxxx-xx-xx-xx". I would like to use a wildcard, so I changed my where statement to the following:

Where actv.ProjID + '-' + actv.Activity = @project + '%'

But this returns nothing and I am not sure why. I don't get any errors, just no results. Anyone got any ideas as to why?

Thanks in advance!

View Replies !   View Related
Query On SQL CE 3.5 With Wildcard

I have a problem with SQL CE 3.5 and VS 2005. When I execute query with parameters and wildcard, I have an error : FormatException.
Here is my query with parameters :
Parameter: @Section1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 52.
Parameter: @Entites2 : String. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "%D%".
Parameter: @Dossier3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @Rang4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.
and my error :
[System.FormatException] = {"@Entites2 : %D% - FormatException"}

If I execute query in Query Analyzer (without parameter : values directly on query) there is no errors.


View Replies !   View Related
SQL Wildcard - Parameters And Gridview
Hi There,
I have created a gridview and a text box search field to list records from my database based on a search query.
This works fine but what I would like to do is enable a wildcard search.  So for example - if I enter fluff  a record with the value flufflebot will still appear in my resultant gridview.
My current SQL query looks like:
SELECT     dbo.myTable.*FROM         dbo.myTableWhere Field1 = @Field1
Where @Field1 is a parameter returned from user input
to enable wildcard:
SELECT     dbo.myTable.*FROM         dbo.myTableWhere Field1 like @Field1 <- this is where i get stuck - I need to add % to the end of that to enable a wildcard search - but that breaks the parameter
know this must be simple - appreciate any suggestions

View Replies !   View Related
WildCard (%) Search Issue
Hi all, I am currently using SQL Server 2000and am having some issues when searching a CHAR column and using the WildCard character '%'. The problem is that when I use this WildCard character, the results returned (if any!) are incorrect. For example, I know that there are many rows in the DB that have the following data in a column, 'ABERYSTWYTH CEREDIGION', but when I using the following SQL...'(WHERE (UPPER(AddrLine3) = 'ABERYSTWYTH%')'  I get no results returned.Is there a known issue with the WildCard character in SQL Server 2000?ThanksTryst

View Replies !   View Related
% Wildcard Include Nulls
I have a query with 4 parameters:
Employee Number
Officer Code
There are no null values in Name, location and Employee Number.  However, all employees do not have an officer code.  So in my query I use a where clause that says WHERE OfficerCode LIKE '%'+@Param4+'%'.  I use a % for the default value in my data control.  The only problem is that the only records returned have non-null values in the OfficerCode field.  How can I use a wildcard for a default value and return all records (null and non-null)?

View Replies !   View Related
Wildcard In Simple Sql Search.
I'm very new to SQL so please forgive my ignorance...
I've made a simple .net search page which queries an sql database with the following query, (in a stored procedure):
SELECT     Category, Number, RegisteredUser, DeptName, Surname, Forename, Site, IDFROM         tblTelephonesWHERE     (@surname IS NULL OR                      Surname LIKE @surname) AND (@site IS NULL OR                      Site = @site) AND (@deptname IS NULL OR                      DeptName LIKE @deptname)
This works fine, as expected if i leave fields null or enter an exact match, but I (of course) have to add a wildcard in my search string for a wildcard search. For example, looking for 'duncan' i need to enter 'du%' or 'duncan'.
What I really want is for all searches to have wildcards behind them so only the first few characters need be inputted, and I could just search for 'd' or 'dun' without adding the '%' to get 'duncan'. I think I am aware of the implications of this approach and do want to go ahead as there are only about 850 records.
Any help or links to useful articles would really be greatly appreciated.

View Replies !   View Related
Conditional Where Wildcard Problem

I have a problem using the LIKE operator in a stored procedure. I have simplified the script so that it runs in query analyser and still have the same problem. The script is:

DECLARE@FirstName varchar (50)

SELECT @FirstName = 'B%'

WHERE PhoneList.FirstName LIKE CASE @FirstName WHEN '' THEN PhoneList.FirstName ELSE @FirstName END

This code produces no rows in the result. However if I change the second line to:
SELECT @FirstName = 'Ben'
Then I get all of the rows with 'Ben' as the first name. If I change it to:
SELECT @FirstName = 'Be%'
Then I get all of the rows with three character first names beginning with 'Be'. If I change it to:
SELECT @FirstName = 'B%%'
Then I get all of the three character first names beginning with 'B'.

I need the conditional where so that if an empty string is passed it returns every row, which works fine as it is.

The % wildcard appears to be operating the same way as the _ wildcard. Has anyone seen this before?

This is SQL Server 2k SP3 on Win2003 server.


View Replies !   View Related
3 Tables Wildcard Searches

I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.

There are 3 tables involved, the jobs, the applicants and the applications.

I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.

There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.

My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?

So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?

Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.

So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.

Thanks in advance.

View Replies !   View Related
Compare Two Columns With WildCard
I am trying to compare columns in two tables with a wildcard character.

One table: Other Table:
ID Col1 | ID Col2
1 1 1 1A
1 1B
2 2 2 2A
3 3 3 3A
4 5 4 5A
4 5B
4 5C
5 7
6 27
7 50 7 50A
I want to writing something like:

SELECT Table1.ID, Table1.Col1, Table2.ID, Table2.Col2
From Table1, Table2
WHERE (Table1.ID = Table2.ID) AND (Table2.Col2 LIKE Table1.Col1%)

which obviously does not work.

basically "column2 Text%" so if ID = 1, Col1 = 1 => will have the following comparisons turn out true:

1A LIKE '1*'
1B LIKE '1*'

How can I do a comparison like this?

View Replies !   View Related
CASE Statement Using Wildcard
im trying to use wildcard in CASE statement with no luck.

im use this:

WHEN '%price%' then 'Price'
ELSE 'No price'

the colum is varchar

The expected result would be 'Price' in every row that contains price (with wildcard around) but every line shows 'No price'
Everything works fine if i don't use wildcard and put in the whole string.

thx in advance //Mr

View Replies !   View Related
SQL Parameter Query && Wildcard
I just migrated a stand alone MSAccess2003 .mdb to an .adp Access Data Project. In the former .mdb I had a parameter query to search for a portion of a 17 character part number, primarily the 2 grouping of 4 digits as in (00-0000-XXXX-0-00). In Access the query was written as

LIKE *&[Enter a portion of the part number]&*

Then we ported to a web based portal using MSAccesss DAP (data access pages) and I was told due to ADO vs DAO the query had to be rewritten using the % symbol as in

LIKE %&[Enter a portion of the part number]&%

So far so good. However with the migration to a SQL Server and ANSI-92 compatible wildcard characters this has changed. The new query looks like this

LIKE @Enter_a_portion_of_the_part_number +n%

The problem I am having is the prompt for data entry works but the wildcard will only return matching values from the first digit place holder. Some of the books I have read said to use the Under Score character to move the search criteria to the section I want to search which would be starting with the 9th character (00-0000-XXXX-0-00) as in

LIKE @Enter_a_portion_of_the_part_number +n________%

However the Under Score only works with Alpha Characters and not Numeric.

So how do I write a SQL Server Query to perform like it did in Access?

View Replies !   View Related
Page 2 - Wildcard Misuse?
Quote: Originally Posted by Frank Grimes thanks for the help. you're welcome

i hope you are not in that hot water you were talking about

View Replies !   View Related
Wildcard Column Selection?
Is it possible to have some thing like:

SELECT columns beginning with 'a' from mytable

kinda thing?

View Replies !   View Related
Storedproc For Wildcard Search
Hi someone please help me.

i have a serach page which have 4 textboxes.
passing this textboxes as parameters to storedproc iam searching the value.
filling atleast one textbox should fetch the value.

i have stored proc for searching it using normal column values but i want it do using wildcard search also.


ALTER PROCEDURE [dbo].[search1]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))

DECLARE @MyTable table (CNo varchar(255))


Select CNo From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))

--Now do your two selects

FROM customer c
INNER JOIN @MyTable T ON c.CNo = T.CNo
Select r.*
From refunds r
INNER JOIN @MyTable t ON r.CNo = t.CNo


if the user enters lastname s*

using same storedproc can i insert wildcard search.

how can i do that please some one help me.



View Replies !   View Related
Replication With SSl And Wildcard Certificates

Need to replicate mobile device that uses a wildcard certificate. Heard that ms windows mobile 5.0 does not support wildcard certificates.Is there a solution around this using

Microsoft CF 2.0

Microsoft SQL Mobile 2005

Microsoft SQL Mobile 2005 Replication

View Replies !   View Related
Challenging Likes Wildcard
Hi all,

I need to search the field containing the word I enter, but this word is bracketed by "{{" and "}}". For example, I would like to search the keyword apple, I need to search by the following sql statement.

select name from table where name likes '%{{apple}}%'

But the case is that that column may contain some space between the bracket and the keyword. i.e. {{     apple  }}. How can I write a sql statement to search name from the keyword?



View Replies !   View Related
Installing Wildcard Certificate

I have been puzzling over this for the past 2 days with absolutely no success. I have an sql server ( that I am upgrading to sql 2005. I am trying to install our ssql certificate (which works fine in iis) in sql. The major dilemma lies I think in the fact that the cert is a wildcard certificate (covering *

I have tried all of the suggestions mentioned online and in these forums, including installing the hash in the rgeistry manually, using httpcfg.exe to setup cert usage for our ip address and port, etc etc. Sql server is using as a domain account (bar/SQLServer) and the cert is installed in the machine store (though I also tried installing it in the user store wihtout luck).

Any guidance or advice anyone can provide would be greatly appreciated.


View Replies !   View Related
Wildcard In Stored Procedures

This oneis givingmeheadaches because I thought it was very easy to create it
I 've created a SELECT stored procedure inside SQL 2005 Express, which filters data using multiplevariables...
I am executing the SP inside SQL Management Studio but I don't get any results unless I pass the '%' value again for
all fields that I don't want to filter.
I want the SP to return allfields when a variable is null, so I won't need to passtwice thewildcard
this is an example (a part of) the SP code:


@pel_kodik nvarchar(15),

@pel_onom nvarchar(50),




SELECT pel_id, pel_kodikos, pel_onoma

FROM tbl_pelates

WHERE (pel_kodikos LIKE @pel_kodik + N'%' OR

pel_kodikos IS NULL) AND (pel_onoma LIKE @pel_onom + N'%' OR

pel_onoma IS NULL)

View Replies !   View Related
SQL Syntax Assistance Needed For A Wildcard
I have the following SQL Statement and I am trying to pass in a wildcard for the "Branch Parameter".  The Branch Parameter is a numeric field.
I need (Branch = %) wildcard to select all the records.  Can someone tell me the proper syntax for this?
SELECT     EmployeeID, CountryCode, FName, LName, Branch, Title, Status, Required, Total, PercentageFROM         dbo.vw_Compliance_Percentage_EmployeeWHERE     (Branch = @branch)ORDER BY Branch
 I receive the message failed to convert parameter value from string to an int32

View Replies !   View Related
Wildcard To Select All Using Numeric Field
Hi all,I've set up a page with a gridview, and I'm trying to create a query based on three parameters and to display all records when the page loads.I have created the first two parameters no problem, but I'm having problems with the last one.The parameter is to be populated by a dropdownlist, where the selectedvalue is numeric.  I have done  a search and found that I can't use the '%' wildcard as it is for a string data type.I have read somewhere that to get around this I can use the CHAR() function to convert to a character, I have tried this without success.When creating the ControlParameter the datatype gets set to string, which I think is not working becuase the input has to be an integer for it to conver to char, am I right?This is the query, and it is the @Application parameter that I'm having this problem with. SELECT dbo.Issue.IssueID, dbo.Issue.ReportedBy, dbo.Issue.ShortDescription, dbo.Issue.DateReported, dbo.Issue.Status, dbo.Priority.Description AS Priority,
dbo.Priority ON dbo.Issue.Priority = dbo.Priority.PriorityCode INNER JOIN
dbo.Application ON dbo.Issue.Application = dbo.Application.ApplicationID
WHERE (dbo.Issue.Status LIKE '%' + @Status) AND (dbo.Issue.AssignedTo = @AssignedTo) AND (CHAR(dbo.Application.ApplicationID) LIKE '%' + @Application)
ORDER BY dbo.Priority.PriorityCodeHas anyone managed to do this another way?Thanks 

View Replies !   View Related
How To Use Wildcard Characters With Control Parameters
Hi guys,
I have a gridview and textbox. I can filter the grid just fine with the textbox, but I would like to allow the user to type in part of a name.
I thought something like this would work but it doesn't:
Emp_Name LIKE '%'+@Emp_Name+'%'
Thank you.

View Replies !   View Related
Reverse Wildcard Searches Impossible?
I have researched newsgroups and the web very thoroughly andunsuccessfully for a solution to what I believe is a very commonproblem. I know it's easy to do wildcard match against data in DB(using LIKE and "%" and "?").But is it possible to match a concrete string against a database ofwildcarded data? ("%" and LIKE do not work). For example:CREATE TABLE blacklist (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,pattern VARCHAR(255) NOT NULL/* ... */);INSERT INTO blacklist (pattern) VALUES ('');Is there a select query that would match address mars2.foobar.comagainst this row?Some people claim that the following query will work. I have tried itand it's not true on either Oracle or SQL server.SELECT * FROM blacklist WHERE '' LIKE pattern;Some people suggest breaking up the blacklist table into N varcharfields for each domain segment and then representing a wildcardcharacter as a NULL and use isNull to match it. This does work to anextent. However, a) it seems really ugly, b) does not allow arbitrarywildcarding (eg, and c) this is something the DBshould do out of the box.Please help! Humanity will be greatful as there's currently nosolution to this anywhere on newsgroups.

View Replies !   View Related
Full-text Search And Wildcard

i want to search for "any phrase here*" where i want it to match the exact same phrase and followed by anything. but the problem is that when using the following syntax: contains(myTbl,'"my phrase here*"') i will get results like "my123 phrases here" so the wildcard will be applied to the consecutive words and not the last word.

How can i search for an exact phrase followed by anything?

ps: i cant omit the wildcard becoz the last word is not exact

so treating the space as any other character and not as a seperator in the search_expression may be a solution but how to do it


View Replies !   View Related
Full Text Search(Using Wildcard)...

I have a doubt about using a wildcar(% or *).
Can we use the the wildcard in query in the following way...

Select name from abcd where
(name like'%moh%'or name like '%raj%'or name like '%san%')

Is it allowed to use the wildcard at the biginning of the string. If any one says yes :Using the full text search is it possible to improve the query performance.
I thinking of that it is not allowed to use the wildcard at the beginning of the string.
Your comments and recommendation will be highly appreciated...


View Replies !   View Related
Find Literal Underscore - Not Wildcard
In MS Sql 2000 - is it possible to find the literal underscore character (_). Something like:

SELECT * FROM foo WHERE bar like 'a\_%'

where the query would return "a_abc", "a_cba", and "a_lksdfjlkdsjlksjdfl", but not "abc"?

I've tried:
SELECT * FROM foo WHERE bar like 'a\_%'
SELECT * FROM foo WHERE bar like ( 'a' + CHAR(95) + '%' )

Any other suggestions?

View Replies !   View Related
LIKE Query With Table Of Wildcard Strings
The sql below is a simple example of LIKE query with a table of wildcard strings. I hope this helps others.

declare @LikeTable Table
LikeValue nvarchar(50)

insert into @LikeTable (LikeValue) values ('%blah')
insert into @LikeTable (LikeValue) values ('%abc%')
insert into @LikeTable (LikeValue) values ('%edf%')
insert into @LikeTable (LikeValue) values ('car%')
insert into @LikeTable (LikeValue) values ('%ome%')

declare @CompareValue nvarchar(50)
set @CompareValue = 'some value'

select * from @LikeTable where (@CompareValue like LikeValue)

View Replies !   View Related
Wildcard Search On Encrypted Column?
In SQL 2005, is that possible to do a wildcard search on an encrypted column?

Many thanks

View Replies !   View Related
Wildcard Search - Column Value Containing % Character
Hi All,

I have a company table with CompanyName and Address details.
In the Company Name field - I have got companies with names like - 'The 1% Club', '99% Pure Water', 'The 1% Golfer' etc...

I want to search for Companies with % using the LIKE clause - Say for ex.

SELECT CompanyName from Company WHERE CompanyName LIKE 'The 1%%'

I was expecting the above query to return - All Companies starting with 'The 1%' - So from the above list - I expected it to return - 'The 1% Club' and 'The 1% Golfer'.

Unfortunately the query isn;t accepting % in the WHERE LIKE clause except for the wildcard character.

Is there a way out to escape the Wildcard Characters present in the Field Values while searching.


View Replies !   View Related
Wildcard Search On An Encrypted Column?
Is it possible to do a wildcard search on an encrypted column?

Many thanks!

View Replies !   View Related
WildCard Functionality Error In SSRS
Hello All,

When i am calling the Report from ASP.NET getting the below error:
An error has occurred during report processing.
Query execution failed for data set 'dsOpenDefect'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors


@DefectImpact varchar(max) = '',
@DefectNumber nvarchar(255) = '',
@DefectApplication nvarchar(255) = '',
@DefectBusinessFunction nvarchar(255) = '',
@DefectRelease nvarchar(255) = '',
@DefectStatus nvarchar(255) = '',
@Owner nvarchar(255) = ''
set concat_null_yields_null OFF

declare @sql nvarchar(4000)
declare @whereClause nvarchar(max)
declare @sqlWhere varchar(max)
declare @Debug nvarchar(max)

select @sql = '
SELECT Defect.DefectNumber, Defect.DefectApplication, Defect.DefectBusinessFunction,
Defect.DefectRelease, Defect.DefectStatus, Defect.Owner, Defect.DefectImpact FROM Defect '

Set @sqlWhere = ''

if @DefectImpact <> '' or @DefectImpact IS NULL
set @whereClause = ' where DefectImpact '
set @whereClause = @whereClause + ' = ''' + @DefectImpact + ''''

set @sqlWhere =@sql + @WhereClause

PRINT @sqlWhere



ReportParameter[] parm = new ReportParameter[7];
parm[0] = new ReportParameter("DefectImpact", txtValue3.Text.ToString());
parm[1] = new ReportParameter("DefectNumber", txtValue3.Text.ToString());
parm[2] = new ReportParameter("DefectApplication", txtValue3.Text.ToString());
parm[3] = new ReportParameter("DefectBusinessFunction", txtValue3.Text.ToString());
parm[4] = new ReportParameter("DefectRelease", txtValue3.Text.ToString());
parm[5] = new ReportParameter("DefectStatus", txtValue3.Text.ToString());
parm = new ReportParameter("Owner", txtValue3.Text.ToString());

RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
RptViewer.ServerReport.ReportServerUrl = new System.Uri("http://RSName/ReportServer");
RptViewer.ShowParameterPrompts = false;
RptViewer.ServerReport.ReportPath = "/Reports/Subbu_Sample";

Can anyone please help me in this regard.


View Replies !   View Related
Question About Different Query Results With Wildcard
Hi, I'm working with a third party app on SQL Server 2000, and from what I can gather, programmed in C# & VisualFoxPro.

When we search with
Note contains 94949
we get 571 results, when we search with
Note contains 94949*
we get 575 results.

There should be at least a hundred different entries that start with "94949-1" so I expected the query with the wildcard to return something like 680 results, not an additional four rows.

Searching with
Note contains 94949-1*
got 483 results
Note contains 94949-10*
got 0 results

Could someone explain or point me to more documentation on the difference results we get?

View Replies !   View Related
Wildcard Syntax NO RESULTS WHEN Using Preview Data
 I have come across a problem today when working with data sets/table adapter in vs05 for use in
SELECT  ws_Agent.Telephone, ws_Agent.Email, ws_Agent.Name, ws_Agent.AgentId, ws_Agent.forename, ws_Agent.surname, ws_Agent.Acc_PcentreID, ws_ProfitCentre.ShortCode, ws_Branch.Name AS branchname, ws_Branch.ManagerId,             ws_Branch.Fax AS branchFax, ws_Branch.Telephone AS branchtelephone, ws_Branch.Address1, ws_Branch.Address4, ws_Branch.Postcode, ws_ProfitCentre.Telephone AS PCentreTelephone,             ws_ProfitCentre.Status AS PCentreStatus, ws_ProfitCentre.Fax AS PCentreFax, ws_ProfitCentre.Email AS PCentreEmail, ws_Agent.Status, ws_Agent.DisableAccount, ws_ProfitCentre.PCentreID,             ws_Branch.Status AS BranchStatusFROM    ws_Agent INNER JOIN            ws_ProfitCentre ON ws_Agent.Acc_PcentreID = ws_ProfitCentre.PCentreID INNER JOIN            ws_Branch ON ws_ProfitCentre.BranchID = ws_Branch.BranchIdWHERE  (ws_Agent.Status <> 3) AND (ws_ProfitCentre.Status = 0) AND (ws_Agent.forename LIKE  @forename) AND (ws_Agent.surname LIKE  @surname ) AND             (ws_ProfitCentre.ShortCode LIKE  @profitcentreshortcode) AND (ws_Branch.Name LIKE @branchname )
The wildcard syntax works fine when tested in the query builder when passing in % to the parameters
 , but does not work correctly NO RESULTS WHEN using preview data and in the subsequent use of data set in code.
Can anyone please comment on this or is there a setting I have missed?

View Replies !   View Related
Handling Wildcard Characters In Query String

First interaction to the forum.
My Query is :

I had a User Management module in my application where I created a user with name


Now I have a functionality to search for the user existing. For that give the search string or a single character and it finds out all the records containing the character.

How do I go about it as the SP i created for it gives correct results except the following

1. Search for % - Gives all record
2. Search for _ - Gives all records
3. Search for [ - Gives NO record
4. Search for the whole string - Gives NO Record

I handeled a few issues

1. replaced [ by [[]
2. replaced _ by [_]

So issues 2 & 3 are resolved.

Tried replacing % by [%] but did not work

Could someone plz help

Thanks in advance

View Replies !   View Related

Copyright 2005-08, All rights reserved