Wildcard Search - Column Value Containing % Character
May 8, 2006
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.
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.
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
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.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[search1] (@val1 varchar(225), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) AS BEGIN
DECLARE @MyTable table (CNo varchar(255))
INSERT @MyTable
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
SELECT c.* 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 END
I WANT THE SEARCH TO BE DONE FOR WILD CARD CHARACTERS ALSO.
if the user enters lastname s*
using same storedproc can i insert wildcard search.
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...
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
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch ( Id int not null, AllText nvarchar(400) ) create unique index test_tfts on TestFullTextSearch(Id); create fulltext catalog ftcat_tfts;
I am trying to get the do a full text search using the CONTAINS operator and the asterisk wildcard prefix and apparently it is returning inconsistent results. I have a full-text enabled table named Content which contains an AutoId field with a clustering index built on it, a column named ContTitle which is fulltext indexed and a couple of other columns. The fulltext catalog has been fully populated.
When I perform this sample query:
SELECT AutoId, ContTitle FROM [Content] WHERE CONTAINS(ContTitle, ' "Live Band From Colombia" '), it returns me a row, however when i change the sample query to include the asterisk wildcard behind the search phrase:
SELECT AutoId, ContTitle FROM [Content] WHERE CONTAINS(ContTitle, ' "Live Band From Colombia*" '), it returns no rows.
I tried the to change the search phrase to "Excellence Service Award", for e.g. SELECT AutoId, ContTitle FROM [Content] WHERE CONTAINS(ContTitle, ' "Excellence Service Award" '), and SELECT AutoId, ContTitle FROM [Content] WHERE CONTAINS(ContTitle, ' "Excellence Service Award*" '),
both queries will return rows with the content title Excellence Service Award
Does anyone know if I'm missing out on any anything, like the full text indexed is not populated correctly for e.g? It's just weird that the same kind of search will work on some rows and not for the rest. Thank you very much.
I am developing for a customer and they want a search facility that uses boolean logic and special characters. So they want to be able to add "AND" "OR" "NOT" "*" and "?". And for this to effect the search in the predicted way and ranked. I was wondering if there is any examples of this type of search implemented?Â
I was looking for a way to use a wild card on a date column, but could only convert the file first then use the wildcard Is there another way of conducting the wild card search on a date column without conducting a conversion on the specified column? Sybase has the ability to use a wildcard on the datetime column so I would assume SQL SERVER does too.... Right? I can see that there are some workaround to get the information I need . I have conducted multiple searches and I still cannot find a suitable answer. Anyway, below is some links where I received some of the information:
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name. CREATE TABLE Names (FirstName NVARCHAR (50), LastName NVARCHAR (50)); I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character. I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding. I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View. I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters. Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also? Thanks
int codePage = 950; StringBuilder message = new StringBuilder(); Encoding targetEncoding = Encoding.GetEncoding(codePage); byte[] encodedChars= targetEncoding.GetBytes(str); . message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':"); for (int i = 0; i < encodedChars.Length; i++) { message.Append("Byte " + i + ": " + encodedChars); }
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars)); Console.Writeline(message.ToString());
Is there an easy way to perform a SELECT where you haveblah LIKE 'asdf'but instead of just returning all the asdf's, it also looks for àsdf,ásdf, âsdf, etc?
I have a table containing records of criminal convictions. There are over 1M records and the only change is additions to the table on a monthly basis. The two columns I need to deal with are convicted.NAME and convicted.DOB
I have a second table that has 2 columns. One is the name of the defendant and the other is the birth date. This would be monitor.NAME and monitor.DOB
There are no primary keys or any other way to join the tables for this search I want to do.
I would like to be able to put a name in the "monitor" table and run a query to see if there is a match in the convicted table.
The problem I am having is middle initials or names. If I want to monitor.name = 'SMITH JOHN' it will return the results fine. The problem I am having is if the conviction is in the database as 'SMITH JOHN T', or 'SMITH JOHN THOMAS'.
How can I use the monitor table with a 'LASTNAME FIRSTNAME' and return results if the convicted table has a middle initial. I tried with a JOIN:
select distinct convicted.* from convicted join monitor on monitor.name like convicted.defendant and monitor.birthdate = convicted.dob
I am developing one web site, in that I can upload files to sql server, it will save the uploaded file in binary type and we can download the files from there. Upto this everything is working perfectly. But now my problem is - i have to implement the wildcard character search in this site. The file name and extension will save on the database in different columns, and one more thing is file name is unique. I confused to implement wildcard search in this site, where i can implement this type of search in database or in my application with c#? So please help me to do this. I dont know that this is the right place to post this question or not. If it is not right place, please guide me to where I can post this. If it is right place, please help me to implement wild card search.
I had a situation that required me to set up SQL Server to do full text search against both English content and Chinese content. I am not sure if it's achievable in SQL server environment. Any help is appreciated.
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
I saw the strangest thing when I imported some data into a columntoday. One column contained musical notes instead of data. Has anyoneever seen anything like this? I have no idea where this came from.Thanks in advance.
When generating a check constraint to guarantee that a character column cannot be blank is it best to use comparison operators such as col1 <> '' or to use LEN(col1) > 0? Note that the column in marked as not nullable.
I have a column that returns client numbers. The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization. In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number. EG: client #4334.1 would become 4334 for client # and 1 for categorization number or Client #424561.3 would become 424561 for Client # and 3 for categorization number. I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns. Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second. Is it possible to do this? I really need help on this one. Thank you :)
i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.
I am trying to determine how I can select only the rows with alphabetical characters in that column.
I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.
I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee Column: Name Name ------- edwardneuman!"<] mikemoreno)'>$: JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name ------- edwardneuman<<<< mikemoreno<<<<< JeffJensen<<<<
Hi guys/ladies I'm still having some trouble formatting a select statement correctly. I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database. Here is my select statement cut down to the most basic elements. SELECT commentFROM informix.ipr_stucomWHERE (comment > 70) The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error "Character to numeric conversion error" How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values? I have tried case with cast and isnumeric... I don't think that I have the formating correct. I have also used: WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F')) This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
I have a table with calendar months as columns with values in. I update this table for each month as new data comes in for each month. I need to be able to examine the table and return for each row the last column with data in. ie row1 has data for apr/may/jun need to return jun row2 has data for apr/may need to return may row3 has data for apr/may/jun/jul need to return jul...
Hi All, I use SQL Server 2005, I have more than 15 databases, each database has got more than 30 tables, and each tables has got more than 15 columns some of them 10, 30 it depends. My concern is, if I want to a certain column (example, Line_No) How could i search it? It is time taking to trace out each db and table. The thing is if i forget it in which table is this column how do i found it? is there a search for column level in sql server 2005? Please help if any.
