Postal Code Search And LIKE Statement

Oct 11, 2006

I'm trying to create a form that allows someone to find an
address from a postal code search in the database. My query works exactly as I’d
like in query builder using the following select:



Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@POSTCODE+’%’





I then pass the user entered post code to the select statement
which is executed. However, I’m getting some odd behaviour. Assuming there is
an address in the database with the post code
 
“LS11 0ES”...
 

If I search for LS11, nothing is returned;

If I search for LS11%, nothing is returned;

If I search for %LS11%, nothing is returned;

If I search for %LS11%%, the data is returned.

If I search for %LS%1%%, the data is returned (as is LS21
0ES etc. etc.).

 

However, I want the user to be able to enter shorter search
strings and it to pull all the data back out, so they can enter a substring
such as LS and it will pull out all the data without the users needing to enter
the full pattern of % symbols.



If I go into query builder (in visual web developer) and
enter just “LS” it works as I’d want, but not when pulled from a web page.



Any ideas?



Thanks

View 3 Replies


ADVERTISEMENT

SQL Server 2008 :: Split Postal Code Range Into Single Row

Jul 8, 2015

I got a table with organisation codes with postcal code ranges, from-to.

Example:
Organisationcode, startpostalcode, endpostalcode
001 52005249

I would like to generate rows for this range like this:

001 5200
001 5201
001 5202
.....
001 5249

The table looks like this:

SELECT OrganisationCode, PostcalCodeStart, PostalCodeEnd
FROM dbo.DimOrganisation

View 4 Replies View Related

Distance Between Postal Codes

Jul 23, 2005

I'm looking to find out how I'd go about setting up a database where avisitor to my site could punch in their postal code, and find out how farthey are from another postal code. For example, AutoTrader has this featureI believe to tell you how far the vehicle is from you. Dating sites havethem so you can do proximity searches.Anyone have any ideas where I could start? I'm thinking the post office,but if anyone else has suggestions, I'm open to hear them.Thanks!

View 4 Replies View Related

Using Specific Criteria For Exact Postal Codes?

Feb 20, 2012

I have to find the number of orders sent to two different postal codes but to get all of the right columns, I have to use 2 different tables.

So, I used WHERE to connect the two tables with the primary keys, but where do I use the specific criteria for the exact postal codes?

View 1 Replies View Related

Help With Search Code

Mar 17, 2004

I have been using the following code on a search page for some time, is has worked very well. We recently changed our database to support multiple addresses for each client. So I added the INNER JOIN on the tblClientAddresses. But now when I try to search on a ID I get an ambiguous cloumn name error on the ID. Can anyone see how I could correct this?

Thanks for any suggestions,


Sub BindDataForPaging(ByVal sortExpr As String)
Dim MyConnection As SqlConnection
Dim MySQLAdapter As SqlDataAdapter
Dim DS As DataSet
Dim ConnectStr As String
Dim WhereClause As String
Dim SelectStatement As String

If SearchLastName.Text = "" And SearchFirstName.Text = "" And _
SearchID.Text = "" And SearchCompanyName.Text = "" And _
SearchSal1.Text = "" And SearchComment.Text = "" And SearchAddress.Text = "" Then
Message.Text = "You didn't enter any search parameters. Try Again."
Exit Sub
End If

WhereClause = "Where "
If SearchLastName.Text <> "" Then
WhereClause = WhereClause & "[LastName] Like '" & _
SearchLastName.Text & "%" & "' AND "
End If
If SearchFirstName.Text <> "" Then
WhereClause = WhereClause & "[FirstName] Like '" & _
SearchFirstName.Text & "%" & "' AND "
End If
If SearchID.Text <> "" Then
WhereClause = WhereClause & "[ID] = " & _
SearchID.Text & " AND "
End If
If SearchCompanyName.Text <> "" Then
WhereClause = WhereClause & "[CompanyName] Like '" & _
SearchCompanyName.Text & "%" & "' AND "
End If
If SearchSal1.Text <> "" Then
WhereClause = WhereClause & "[Sal1] Like '" & _
SearchSal1.Text & "%" & "' AND "
End If
If SearchComment.Text <> "" Then
WhereClause = WhereClause & "[Comments] Like '" & "%" & _
SearchComment.Text & "%" & "' AND "
End If
If SearchAddress.Text <> "" Then
WhereClause = WhereClause & "[Address] Like '" & "%" & _
SearchAddress.Text & "%" & "' AND "
End If
If ClientTypeDrop.SelectedItem.Text <> "" Then
WhereClause = WhereClause & "[CLientType] Like '" & "%" & _
ClientTypeDrop.SelectedItem.Text & "%" & "' AND "
End If
If Right(WhereClause, 4) = "AND " Then
WhereClause = Left(WhereClause, Len(WhereClause) - 4)
End If

SelectStatement = "Select *,A.Address FROM tblClients INNER JOIN dbo.tblClientAddresses A ON dbo.tblClients.ID = A.ID " & WhereClause & " ORDER BY " & sortExpr

Try
ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
MyConnection = New SqlConnection(ConnectStr)
MySQLAdapter = New SqlDataAdapter(SelectStatement, MyConnection)
DS = New DataSet
MySQLAdapter.Fill(DS)
MyDataGrid.DataSource = DS
MyDataGrid.DataBind()
Catch objException As SqlException
Dim objError As SqlError
For Each objError In objException.Errors
Response.Write(objError.Message)
Next
End Try

End Sub

View 4 Replies View Related

Search Code

May 2, 2005

Hello.  I have gotten my search page to work when a user enters in the entire loan number.  I want to give them the option to enter in a partial loan number.  I have the following code for Access database.
 
If Page.IsPostBack Then
            MySearch = "loanno Like '*" & txtSearch.Text & "*'"
        Else
            txtSearch.Text = ""
End If
 
I need to convert this for a SQL database search.  I tried to substitute the * with % and I am still getting an error.  Does anyone know how to fix this problem. 
 
Also, It would be nice to make MySearch reference a stored procedure.  This would make the page much more flexible and powerful.  I am open to any recommendations.  Thank you.

View 4 Replies View Related

SQL Code To Use For Additional Search

Jul 6, 2005

I have code on a website that does a search to an Access database based on what the person enters as the search criteria.  My SQL code uses a like % statement for all the fields in the search criteria.  The problem is that the first and third search fields do no return the search criteria that is asked for.....The 2nd search field (Description) returns whatever is asked for.....   I did read up and found that the data must be character for the like statement.  Anyone have any other clues or where I can read up on this???????HTML SEARCH CODE:          <table ALIGN="CENTER" width="408" bgcolor="#FFFFFF" cellspacing="0" cellpadding="5">        <tr>    <td width="400" bgcolor="#3366cc" colspan="2">    <form method="post" name="DaForm" action="Makeit.asp" align="center">      <div align="center"><center><table border="1" width="350" cellpadding="3"      bgcolor="#3366cc">        <tr>          <td align="left" width="189"><select name="TypeSearch" size="1">            <option selected value="PartNumber">Part Number</option>            <option value="Description">Description </option>            <option value="Mfg">Manufacturer</option>          </select></td>          <td width="310" align="right"><input type="text" size="30" name="DaInBox"></td>        </tr>        <tr>          <td colspan="3" align="center" width="507"><div align="left"><p><input type="submit"          name="B1" value="Search!"><input type="reset" name="B2" value="Clear">          </td>        </tr>      </table>      </center></div>    </form>    </td>  </tr></table>Makeit ASP COde:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Your Search Results!</title>
<meta name="Microsoft Border" content="lb, default">
</head>
<body>
<%
Dim MySql
 
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open("parts")
MySql = "SELECT * FROM parts"
If Request.Form("TypeSearch") = "Part Number" Then
MySql = MySql & " WHERE PartNumber LIKE '%" &_
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") = "Description" Then
MySql = MySql & " WHERE Description LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
Dim rs
Set rs = new Server.CreateObject("ADODB.Recordset")
rs.Open MySql, cn, 0,1
%>
<% If rs.BOF and rs.EOF Then%>
<h2 align="center">We did not find a match!</h2>
<%Else%>
 
<%If Not rs.BOF Then%>
<h2>Here are the results of your search:</h2>
<table BORDER="0" width="100%" cellpadding="3">
<tr>
<th bgcolor="#0366cc"><font face="Arial" color="#000000">Part Number </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Description </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Manufacturer </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Qty </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Condition </font></th>
</tr>
<%
Do While Not rs.EOF
%>
<tr>
<td><%=rs("PartNumber")%>
<%=rs("Description")%>

</td>
</tr>
<% rs.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%>
<p>&nbsp;</p>
</body>

View 1 Replies View Related

Search Sp Code For Text Value

May 10, 2000

I have a series of stored procedures defined in a SQL7 database and want search through all their code to find a particular text value (say the contents of a raiserror message for exampl). How can I do this without having to go into each one seperately and scroll through the code? Is it possible to do it from within SQL itself?

Thanks!

Steve Rock

View 1 Replies View Related

Site Search Code Help

Dec 27, 2006

I'm writing a search for our site and I'm running into a few problems.When I run the query like this, it runs just fine:Declare @Keywords varchar(2000)Select @Keywords = 'modern trial advocacy canada'Select product_id, name, count(name) hitsFROM estore_productsINNER JOIN sequenceON estore_products.name like '%' +Substring(' ' + @keywords + ' ',seq,CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)+ '%'WHEREseq <= len(' ' + @keywords + ' ') andSubstring(' ' + @keywords + ' ', seq - 1, 1) = ' ' andCharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq 0Group by estore_products.product_id, nameORDER BY Hits DESCBut when I add another column (for example a column called description)from the select statement I get this error:The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.Long story short, what do I need to do to select more columns for thefinal output?Thanks,Ryan

View 4 Replies View Related

Search All Code For Specific Keyword

Mar 14, 2008

This will work on SQL Server 2005 and later.
Since the code is building an XML string, keywords overlapping the magic 4000 character limit are fetched!SELECTp.RoutineName,
'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]
FROM(
SELECTOBJECT_NAME(so.ID) AS RoutineName,
(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body
FROMSYSOBJECTS AS so
WHEREso.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X')
) AS p
WHEREp.Body LIKE '%YourKeyWordHere%'E 12°55'05.25"
N 56°04'39.16"

View 5 Replies View Related

SQL Full-Text Search In VB Code

Jul 20, 2005

I'm trying to execute a full-text query from a vb.net web application.The problem I have is that in SQL Server, the syntax for a full-textsearch isSELECT *FROM tableWHERE CONTAINS( *, ' "searchstring" ')For whatever reason, VB won't run that search string unless I eliminatethe double sets of quotes:SELECT *FROM tableWHERE CONTAINS( *, "searchstring")This will not work for a search phrase - only single words, and it alsowill not work for a wild card:SELECT *FROM tableWHERE CONTAINS ( *, "search*")The above does not work - I must assume that the double quotes areneeded for both wild card searches and exact phrase searches -The problem of course is that in vb code the SQL string is alreadyenclosed in search quotes - the single quotes are then used to indicatedouble quotes within the search.I have tried using two single quotes on either side of the string, butit doesn't seem to work. ie:"SELECT * FROM table WHERE CONTAINS ( ' '" & textbox1.text & "' ')"Does anybody have any ideas?Trevor Fairchild*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

T-SQL (SS2K8) :: Search Based On Table Name And Add Code To Existing Stored Procedures

Jun 30, 2014

Below is sample SQL:

SQL_1: Creates a database
SQL_2: Creates 2 stored procedures

Now, I need to search database SP`s for Table2 t2 ON t2.CID = t1.CID and ALTER them with Table2 t2 ON t2.CID = t1.CID.

INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'SQL_1:
USE [master]
GO

[code]...

View 6 Replies View Related

SQL Statement For Search

May 4, 2004

I am developing a simple search project against “Job Advertise� table on SQL server 2000. I create search page which has a textbox to input search keywords, and a listbox can multiple select job location. The second page request these input parameter and as a SQL statement to retrieve the search results. The search results show on Datagrid of second page.

I test the search, the SQL Statement as below:

SELECT AdID, JobTitle, JobCompany, JobLocationCity, JobLocationState,
JobLocationProvince, JobLocationCountry, AdPostDate FROM JobAd
WHERE AdActive = '1' AND AdExpireDate >= ‘4/29/2004’ AND JobKeyWords
LIKE '%computer%' OR JobTitle LIKE '%computer%' AND JobLocationCountry
= 'USA' AND JobLocationState = 'NJ' OR JobLocationState = 'NM' OR
JobLocationState = 'NY' ORDER BY JobTitle

The problem are: 1, Some Ad that expiredate < DateTime.Today and AdActive = 0 still show on Datagrid. 2, Some AdLocationCountry = ‘Canada’ show on Datagrid. 3, Some Ad no relation with computer are still retrieved.

I think because after Where, there are many OR condition, so cause those problems. How can I solve those problems?

I think maybe I should do (SELECT AdID, JobTitle, JobCompany, JobLocationCity, JobLocationState, JobLocationProvince, JobLocationCountry, AdPostDate FROM JobAd
WHERE AdActive = '1' AND AdExpireDate >= DateTime.Today) first and put results in temp one table, then do search the Keyword against temp one table and put the results into temp two table, then so search the Job Location against temp two table, finish by get the results to bind to Datagrid. If I should do this, how to write the SQL statement? Dose has a better way?

Anyone who have experience on this part, please give me a suggestion and help.

View 4 Replies View Related

Need Help On A Search Statement

Jun 25, 2003

I’m trying to conduct a search on the sql table to find a field that is blank within a specific time frame. The search I tried was
SELECT *
FROM Receiving
WHERE (RecipientLastName = N’ ‘)
AND (RecDate >= ‘03262002’) AND (RecDate < ‘12062002’)

But I get an error when I try this, Now I also tried the same search but left off the last “AND” statement (AND (RecDate < ‘12062002’)) and I got back a ton of entries. Is there a way I can narrow this search down? Thanks

View 3 Replies View Related

Search Statement

Sep 7, 2005

Hi i want to do a SQL statement where if i enter a word for example 'SQL' then it will display all the info that match with the word SQL from the entire database.

I will have something like this
SELECT * FROM (?) LIKE (my textbox)

What should i put after FROM as i want to select the ENTIRE database. Is there something like * for All?

Thanks

View 3 Replies View Related

Sql Search Statement

Dec 12, 2004

I'm searching a table based on 4 possible search criteria...city, name, date and type. The search can use just one of the search criteria or any combination of the four...i.e. city and name or city and type or name, date and type, etc.

The statement for one search criteria could be written like the following:

Select *
From dbo.myTable
Where field = 'myvar'

myvar = Request.Form("fieldname")

What would be the way to construct the statement to allow searching for any combination of the 4 search criteria?

Thanks in advance,
-Dman100-

View 9 Replies View Related

Best Way To Search For All Records (Using A Case Statement In A SP)

Jul 20, 2005

I have a form with a dropdown or combo box, the user can select <All>or pick a user name. If they pick a user name my where clause worksfine, buts what's the best way to write "Select All" if they choosethe <All>This is what I have so far, but I don't think I should be using theLIKE operator.WHERE tblCase.qarep LIKE CASE @myqarep WHEN '<All>' THEN '%' ELSE@myqarep ENDand tblOffice.officecode LIKE CASE @myoffice WHEN -1 THEN '%' ELSE@myoffice ENDthanks for your help!!

View 4 Replies View Related

How To Code Sql Comments Within Sql Statement

Jun 19, 2007

I want to write a query like this:

select ' select count(*) '+ '/*' + table_name + '*/' + char(10) + ' FROM '+table_name...

The result should looks like this:

--------------------------------------------
select count(*) /* ---- emp_name */
FROM emp_name


---------------------------------------------
query result:


COUNT(*)----EMP_NAME
-------------------
3

-------------------------------------------
But, now I can only got

COUNT(*)
-------------------
3


What's wrong with my original code?

View 4 Replies View Related

Case Sensitive Search In Sql Select Statement?

Feb 28, 2008

Hi ALL,
    How could you make the SQL Select statement query case sensitive, what i mean is i have this select statement below ...
Select * From Staff WHERE Staff.Staff_ID = 'KabirJ' AND Staff.Password = 'KaBir1!'  
and a data row in the staff table that is the following information
Staff_ID      Password
Kabirj          kabir1!
Since the cases in database is different then in the string provided, i want sql to return me nothing, How do i go about and achieve that???????????
Thanks
Kabir

View 2 Replies View Related

SQL 2012 :: Full Text Search Select Statement

Feb 27, 2015

I am doing a web app on the full text search.My select statement using CONTAINS but it doesn't seem to return all the string that contains the search word I included in the query.For example:

Select CustomerID, customername,
from CustomerTable
where CONTAINS ((CustomerID), '"430*")

the query result only return all customer id which has space in front 430 or start with 430
ex: "xxx 430xxx" or "430 xxx" or "xxx 430 xxx"

but did not return customerID which doesn't has space in front of it
Ex: x430xxx

If I use LIKE '%430%', the result return are both have space in front and don't have space in front.

View 1 Replies View Related

Problem In C# Code Behind And With SQL String Date And The Between Statement

May 13, 2008

HiI have a problem to search between two dates, if I use Convert(varchar(10),ContactCreateDate,105) in a single textbow and for a single search date it is okay, but when I use the Convert(varchar(10),ContactCreateDate,105) against two textboxes it did't function.Can anybody help me ?MY codeif (TBContactSearchDateStart.Text.Length > 0 && TBContactSearchDateEnd.Text.Length > 0)
{
strWhere += " AND Convert(varchar(10),ContactCreateDate,105) BETWEEN '%" + TBContactSearchDateStart.Text + "%' AND '%" + TBContactSearchDateEnd.Text + "%'";

View 1 Replies View Related

Code Skipping The Alter Statement!! URGENT!!

May 14, 2002

I have coded an alter statement for adding a column to a temp table inside an sp,but it skips the alter statement while executing the sp! This happens even if I run the same code on query analyzer too. If I use 'GO' statement before the alter command,then it works fine on Query Analyzer.But, I can't use 'GO' in an sp. I am using the sa account. Any ideas on how to fix this??
Thanks.
Di.

View 1 Replies View Related

Wild Search NText And NVarChar In Parameterized Inline Statement

Dec 14, 2004

I want to retrieve data from SQL containing non English character but fail, can anyone shed me some light?

What I use currently:

Dim strSQL As String
strSQL = "SELECT ArticleID, "
strSQL &= "ISNULL(Body, '') AS Body, "
strSQL &= "ISNULL(Subject, '') AS Subject "
strSQL &= "FROM Articles "
strSQL &= "WHERE (Subject LIKE N'%' + @Keyword + '%' OR [Body] LIKE N'%' + @Keyword + '%') "
Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlDataAdapter(strSQL, con)
cmd.SelectCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = keyword
...


I'm not so sure where should I place the letter "N", I use :

SELECT ArticleID,
ISNULL(Body, '') AS Body,
ISNULL(Subject, '') AS Subject,
FROM Articles
WHERE (Subject LIKE N'%SomeNonEnglishString%' OR [Body] LIKE N'%SomeNonEnglishString%')

in Query Analzyer, it works! But it failed in my program... oh my god...

Thanks a lot!

View 4 Replies View Related

Using SqlDataSource To Execute A Select Statement In The Code File???

Nov 27, 2006

Hi you all,
In abc.aspx, I use a GridView and a SqlDataSource with a SelectCommand. The GridView's DataSourceID is the SqlDataSource.
In abc.aspx.cs, I would like to use an IF statement in which if a criterion is not satistied then I will use the SqlDataSource with another SelectCommand string. Unfortunately, I have yet to know how to write code lines in order to do that with the SqlDataSource. Plz help me out!

View 1 Replies View Related

No Response To Code And Query Analyser Cannot Read Statement

Feb 20, 2004

hi

have a problem ...
I tried to test my statement in QA as it returns no dataset ,it gives me empty columns in return,I though it will return all the columns and some records of (lmeyer) as the current user ,who logged in on windows auth,but it return only the columns,is that means my query analyser cannot read the login1 ,column which has the username as (lmeyer)

select * from tstudents where (login1='@param1')

if not what could me wrong in my code ,because I get no response to the sql server




Public Function login(ByVal login1 as string) as dataset

Dim myconnection as new sqlconnection("server=G103-TT03;database=CampusLANDB;Trusted_Connection=yes")
dim mycommand as new sqlcommand ("Select * from tStudents Where login1 = @param1",myconnection)
mycommand.parameters.add(new SQLClient.SqlParameter("@param1",login1))

dim DS as new dataset()


try
myconnection.open

dim adpt as new sqldataadapter
adpt.selectcommand = mycommand
adpt.fill(DS,"tStudents")


catch ex as exception
throw new exception(ex.message)
return nothing
finally
myconnection.close
end try

try
mydatagrid.datasource=DS.Tables("tStudents")
mydatagrid.databind()
catch ex as exception
errorlabel.text=ex.message
end try

return DS

End Function





thankx in advance
pamela mkosana

View 12 Replies View Related

SQL 2000 MS Search: Boolean Search Doesn't Work When Search By Phrase

Aug 9, 2006

I'm just wonder if this is a bug in MS Search or am I doing something wrong.

I have a query below

declare @search_clause varchar(255)

set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'

select * from results

where contains(finding,@search_clause)

I don't get the correct result at all.

If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"

then i get the correct result.

It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?

Anyone know?

Thanks

View 3 Replies View Related

I Want To Write An SQL Statement Which Returns Matching Values But Ignores The First 2 Digits Of The Search

Jan 26, 2007

I want to write a statement something like this
SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/)
But i want the search to ignore the first 2 digits so that it will return e.g
10/11/1234, 09/11/1234  so that it's only matching the last part
Any Help Would be greatly appreciated Thanks

View 6 Replies View Related

In Code Behind, What Is Proper Select Statement Syntax To Retrieve The @BName Field From A Table?

Apr 8, 2006

In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String        Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50)        parameterBName.Value = strName        myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61:         myCommand.ExecuteNonQuery()   'Execute the query

View 2 Replies View Related

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

Mar 29, 2008

 Hi -  I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids:  dbo.Search_Articles        @searchText varchar(150)        AS    SELECT ArticleID     FROM articles    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)    UNION    SELECT ArticleID     FROM article_pages    WHERE CONTAINS(Text, @searchText);        RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter 

View 3 Replies View Related

SQL Search :: Full Text Search With Single Character Returns All Rows

Jul 21, 2015

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;

[Code] ....

View 4 Replies View Related

SQL Server 2014 :: Semantic Search Not Finding Keywords Identified By Full-Text Search?

Nov 6, 2014

I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.

For instance, if I put the following string into a FT indexed table

'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'

The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'

Nor in

'J8999 - Everolimus (Afinitor)'

It did not recognize 'Afinitor' as a keyword.

In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.

why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?

View 0 Replies View Related

Create Site Search Using Sql Server Full Text Search

Jul 24, 2007

would you use sql server "full text search" feature as your site index?  from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...
so i thought create site search table, with the columns:
id, text, url
and to write every thing to this table.
but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...
what do you think? 

View 1 Replies View Related

SQL Search :: Full Text Search Of PDF Files In A File Table

Mar 30, 2013

I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.

--Adobe iFilter 11 64 bit is installed
--The Path variable is set to the bin folder for the Adobe iFilter.
--SQL Developer version 64 bit on both Windows 7 and Windows 8.
USE master;
GO
DROP DATABASE FileTableStudy;
GO
CREATE DATABASE FileTableStudy
ON PRIMARY

[code]....

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved