My use of wildcards thus far has been limited to matching a given
string anywhere in a column as follows:
SELECT * FROM Table WHERE Column LIKE '%string%'
However, I'm wondering if there's a way to do this in reverse. That
is, is there a way to match the column anywhere in the string?
Pseudo-coding it as:
SELECT * FROM Table WHERE 'string' LIKE %Column%
What I'm trying to match is network addresses. Most of the stored
addresses in this table are exact (i.e. ip-1-2-3-4.location.isp.com)
but sometimes they encompass an entire group (i.e. location.isp.com).
When an exact address is given in the code I'm writing, it needs to
match any rows that contain its exact self or contain a shortened
version of which it is part.
HelloI am trying to search 2 columns on a databsae table using a string put into a box, the code i have at the moment is SqlConnection conn = new SqlConnection(SqlDSFindPost.ConnectionString); SqlCommand cmd = new SqlCommand ("SELECT * FROM tblBlog WHERE UserName LIKE @UserName OR Title LIKE @Title; ", conn); cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = '%' + TextBox1.Text + '%'; cmd.Parameters.Add("@Title",SqlDbType.NVarChar, 50).Value = '%' + TextBox1.Text + '%'; conn.Open(); cmd.ExecuteNonQuery(); GridView1.DataBind(); I have tried all sorts of strings and even typeed the string directly into the parameter but never get any results, yet when i type the wildcards directly into the textbox i get the correct rows returned. Can anybody see anything wrong with my code and tell me where i am going wrong, or alternativly point me in the direction of some c# code for searching a database similar to the search box abovei dont do a lot in asp or c# so this is driving me crazy Thanks for looking
I have a need to use wildcards in a sql statement. e.g. select * from tbl where field='%computer%'. How can I substitute the string "computer" for a variable declared in the stored procedure. Procedure Sample @Str varchar(50) AS select * from tbl where field = '%' & @Str & '%' (How do incorporate the wildcard variable @Str?
I want to replace the NNNYYYYMMDDHHMM with a wildcard (for example *), so that import will pull ANY .SDF files in, but it will not run. i get the following:
output ------------------------------------------------------------------------------ DB-LIBRARY error: Bcp: Unable to open host data-file.
I need to replace the use of wild cards in my query with something else which achieves the same thing. The problem is the web application which uses the query does throws an error when using '%' characters. Any ideas?
The following statement appears in the where clause:
AccType.Value like '@Opened_By[%DIST%APP% as Distance and Business Provider, DIST% as Distance, APP% as Business Provider]'
I’m trying to use case statement in my view with wildcards for '%Tradies%', instead of listing all items
WHEN 'Tradies Rebate1' THEN 'Test' WHEN 'Tradies Rebate2' THEN 'Test' WHEN 'Tradies Rebate3' THEN 'Test' WHEN 'Tradies Rebate4' THEN 'Test'
At this moment '%Tradies%' does not work and gives me null values in EventGroup column.
Here’s my statemnt ------------------------------------------ CASE [dbo].[Event].[EventName] --WHEN 'Tradies Rebate1' THEN 'Test' --WHEN 'Tradies Rebate2' THEN 'Test' --WHEN 'Tradies Rebate3' THEN 'Test' --WHEN 'Tradies Rebate4' THEN 'Test' WHEN '%Tradies%' THEN 'Test' WHEN 'Install Products' THEN 'All Installed' WHEN 'Installation Product Conversion' THEN 'All Installed' WHEN 'Installation Products' THEN 'All Installed' WHEN 'BK 3' THEN 'All Bright Kids' END AS [EventGroup], ------------------------------------------
I have a SQL statement which is generated dynamically. I need to know what is the correct syntax for this
WHERE status = 'open' AND salesman = * AND dat = * AND customername = *
i.e. fetch everything WHERE status = 'open'
I know that simply WHERE status = 'open' would do the trick but I need it like the first example because of the way the statement is being generated i.e. this salesmen bit is like this.
If Salesman <> "*" Then sql2 &= " AND salesman = '" & Salesman & "'" Else sql2 &= " AND salesman = *" End If
Hi I'm using the full-text indexing on a table and I'm trying to implement a search where users can search for words and use wildcards themselves. However I'm working on a method so that can enter a wildcard in the middle of a word to get records where they are unsure of the spelling etc. For instance, a search of 'Ste*en' should return results like 'Steven' and 'Stephen' etc. So if they are searching for word 'establishment' they can search for 'estab*ment' and it should return all the records using this query: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*ment"') If I do a wildcard at the end e.g: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*"') I get the results I am looking for. But the middle wildcard does not seem to work as expected even though it is the syntax used on MSDN and other SQL info sites. Is there something I am not doing properly?
I have a stored procdure in SQL Sever that accepts paramteres. I am trying to return rows where parameter that is passed is somewhere in the cuustomer's name. Without the variable the SQL would look like this:
SELECT * FROM tbl WHERE CustomerName LIKE '%Smith%'
I can't figure out how to replace LIKE '%Smith%' with a varible. I tried '%@CustomerName%', ('%' + @CustomerName + '%') and neither works. Any ideas? Thanks
ps my column's type is char(50) and so is the variable so trailing spaces don't matter.
Hi all,I am creating an ASP.NET site, and I'm having lots of issues trying to get wildcards to work with the following query:DECLARE @Status varcharDECLARE @AssignedTo intDECLARE @AppID intSELECT dbo.Issue.IssueID, dbo.Issue.ReportedBy, dbo.Issue.ShortDescription, dbo.Issue.DateReported, dbo.Issue.Status, dbo.Priority.Description AS Priority, dbo.Application.ApplicationFROM dbo.Issue INNER JOIN dbo.Priority ON dbo.Issue.Priority = dbo.Priority.PriorityCode INNER JOIN dbo.Application ON dbo.Issue.Application = dbo.Application.ApplicationIDWHERE (dbo.Issue.Status LIKE '%' + @Status) AND (dbo.Issue.AssignedTo = @AssignedTo) AND (dbo.Application.ApplicationID LIKE '%' + @AppID)ORDER BY dbo.Priority.PriorityCode When running this through query analyser I get the error:Server: Msg 245, Level 16, State 1, Line 5Syntax error converting the varchar value '%' to a column of data type int. Could someone help me understand this please?Thanks
How do Iput wildcards around a number in an sp ? If my user leaves BoxNo blank it will list all boxes
SELECT * FROM tblFiles WHERE ConNo =@strRMUConsignmentNo and FileRef like '%'+@strtxtFileRef+'%' and Subject like '%'+@strtxtSubject+'%' and FileDescription like '%'+@strtxtDescription+'%' and BoxNo like %+@strBoxNo%
Is it possible to use wildcards with an equals statement? Such asSELECT * FROM Table WHERE City = '%' AND State='Ca'Bascially just stating where city equals anything...I know you can do it with a LIKE statement such as...SELECT * FROM Table WHERE City LIKE '%' AND State='Ca'but is that very efficient?The reason I want to do this is because I want to programmitcally set the city, so just ommiting it won't work Also, using City LIKE '%' seems to not include NULL...is there anywayto include NULL as well as anything else? Thanks for your help!
Hey all,I have a datagrid with populated by this query: SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')I have paging, sorting and selection enabled.Now I am looking for a way to use a wild card as a placeholder for the table name in my select statements so I can use the valued selected from the datagrid.Example : SELECT * FROM %TABLENAME%TIAWOOHOO! my first post.
I have a really large table with many Proposal fields and corresponding approval fields.
Propose1 Approve1 Propose2 Approve2
Would it be good practice or even possible for me to select all proposal fields using a wildcard somehow within the select statement. If it is ok, how would I go about doing it?
If I use _reverse_ wildcard search will it always result in a tablescan? Is it possible to get the DB (Oracle or SQL server) to useindexes when doing reverse wildcard match?let's say I have:table email_address (id int, email varchar)with the following entries2, www.%shoes.%3, w%.super%shoes.%4, %webbox.somecopany.comselect id from email_address where 'www.superdupershoes.com' likeemail;this returns 2,3But the query always results in a table scan even if I add an index toemail. What kind of index can I employ in this situation?Please note that this is a _reverse_ search, the opposite of what'snormally done, i.e. select from email_address where email like'www.%shoes.com'.Thanks!- Robert
I have an ASP.NET application where I am using a drop down list which is populated from another table. I have initialized the drop down with a "All" with the value of "%" field and then appended the rest of the data from the table.
I wrote a basic stored procedure which doesn't work exactly the way I want it to:
CREATE PROCEDURE dbo.spGetHistory
@StartDate datetime,
@EndDate datetime,
@MessageCode char(2)
AS
SELECT *
FROM table_name
WHERE (update_date between @StartDate AND @EndDate) AND (message_code LIKE @MessageCode)
If I select any item except for "All" the stored procedure brings back exactly what I want. If I select "All", no rows are returned. I have searched around the internet for a simple solution to this with no luck. Any ideas?
Greetings all trying to get a multivalue parameter to accept either typed in data ex: 111111111,111111112 or if I want to return all id numbers type in %. Problem is when I test it by typing in 111111111,111111112 it throws an error saying " Incorrect syntax near ','.
I can enter 111111111 or % and get results, the error comes when I try to type in two or more id numbers. the parameter in the dataset looks like
where a.id_number LIKE (@id_number+ '%')
any suggesstions? Im sure there are threads out there but Im pressed to meet a deadline and wanted to see if there were any quick solutions
I thought this problem would go away over the Christmas holiday, butof course it did not. I'm trying to write a stored procedureincorporating wildcards, so I can search for variations. Example, ifname 'Smith' is submitted, sproc should retrieve all recordscontaining 'John Smith', 'Zenia Smith', 'Smithfield & Co.' You get theidea.Using SQL Query Analyzer, the queryselect * from filewhere name like '%smith%'works like a charm.But if I write a stored procedure declaring the variable @name andusing a where clause 'where name like '%@name%'', I get zero results.The query doesn't bomb. It just doesn't produce anything - even thoughI know there are records that meet the criteria.Any ideas? Or are sprocs and wildcards incompatible?
Hello, I have what should be a very simple problem, but I cant solve it. I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!)
Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example:
DECLARE @Dv_id nchar(15)
SET @Drv_id = '%'
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE '%'
OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows. The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between: WHERE Drv_id LIKE @Drv_id and WHERE Drv_id LIKE '%' ? The wildcard statement '%' is supposed match everything, correct?? It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL.
I have a complex Foreach loop that needs to operate on files beginning ABC*.* and BCD*.* (but not files beginning CDE*.*). But the enumerator configuration only seems to allow one wildcard. Any suggestions on how I do this? Thanks.
Hi, I am using Foreach Loop to loop through files in a directory... I would like to use more than one wildcards (e.g. *.txt *.log ).. but the container does not seem to work that way. It only takes one wildcard...
Is there anyway i can pass in multiple file extensions ? thanks
I have a smart device project in Visual Studio 2005 that has a SQL Mobile data source. I am trying to create a parameterized query that utilizes 'LIKE' and wildcards. My query is below:
SELECT LocationID, StreetNum, StreetName, rowguid FROM tblLocations WHERE (StreetNum = @StreetNum) AND (StreetName LIKE '%' + @StreetName + '%')
However, when I test this on my PDA, I get the following error:
SQL Execution Error.
Executed SQL statement: SELECT LocationID, StreetNum, StreetName, rowguid FROM tblLocations WHERE (StreetNum = @StreetNum) AND (StreetName LIKE '%' + @StreetName + '%') Error Source: SQL Server Mobile Edition ADO.NET Data Provider Error Message: @StreetName : deerbrook - FormatException
Does anyone know how to add wildcards to a parameter?
STEP1: CREATE TABLE Trace(Statement VARCHAR(MAX)) INSERT INTO Trace VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted from History'), ('Insert into Account Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')
[code]....
now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.
the sql server documentation states that the use of wildcards is allowed by placing an '*' at the end of the search term. I can get this to work OK in the SQL Server 2005 query window, heres an example select ID, SUBSTRING(Title, 1, 100) AS Title, Implemented, Published from Table1 where contains(title,'"Therap*"') ORDER BY Title
this works OK and returns a list ot titles with the word Therapy in the title Im trying to implelemnt this functionalty in a web app with C#. The string is passed to a stored procedure. How on earth do I pass in the quotes ?? Ive tried building the string as normal then adding single quotes on the end, so I get something like retval = txt + "*"; //txt contains the partial word im searching for, then add the wildcard then retval = "'" + retval + "'"; // add the single quotes and pass txt as a string parameter to my stored procedure. It doesnt work. Can anyone tell me what im doing wrong ?? the same query works fine in the SQL query window.
I need to create a stored procedure and incorporate it into a report where the users can look up certain values in comment fields. This is what I have so far but I am getting errors.
Code:
CREATE PROCEDURE [dbo].[SearchAccomplishments] @Enter_Accomplishments text =null, @Beginning_CompletionDate datetime = null, @End_CompletionDate datetime = null AS IF patindex( '*', @Enter_Accomplishments ) > 0
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
