LIKE Pattern Searching Using CONTAINS Or FREETEXT
May 15, 2007
Hi,
We are using FULLTEXT searching for searching the title column in SQL 2005 table.
We are aware that using CONTAINS we cannot search middle of the string like the way we do using LIKE '%text%'.
Is there any alternative way of acheiving this functionality using FULLTEXT search or any other ways of doing this keeping in mind performance.
Any feedback on this is greatly appreciated.
thanks and regards,
Krishna.
View 3 Replies
ADVERTISEMENT
May 8, 2015
I tried creating one simple bar chart report in SSRS, with background fill with pattern "UpwardDiagonal", the stripes on the column when viewed insider report viewer control is quite prominently visible, whereas when exporting the same report to PDF the lines are very thin and dense and hence makes it very difficult to differentiate between one column with pattern inside and one without. I have even tried various other pattern like "LightUpwardDiagonal" and "WideUpwardDiagonal" but all have same result when exported to PDF.why?
View 2 Replies
View Related
Aug 18, 2006
Hi,
I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.
Wildcard
Meaning
%
Any string of zero or more characters.
_
Any single character.
[ ]
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).
Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.
In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:
WHERE
Gift_Date NOT LIKE "####*"
After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:
WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'
using single quotes and the % wildcard instead of Access' double quotes and * wildcard.
Just putting this out there for anybody else that is new to SQL, like me.
Regards,
Patrick Briggs,
Pasadena, CA
View 1 Replies
View Related
Nov 5, 2007
I don't know whether this is the right place to ask this question. I didn't get the answer from sql forums may be someone here can help. I need to use freetext on two columnssomething likefreetext(column1+column2,@Search) this will give error "Incorrect syntax near '+'."Any One know the correct syntax. Thanks
View 1 Replies
View Related
Jan 24, 2005
I have a vb page, which is a simple front end so I can edit text from anywhere.
The person who is going to use it knows no html, and so I am trying to get freetextbox to work.
I am getting the following error:
Server Error in '/' Application.
--------------------------------------------------------------------------------
ExecuteNonQuery: CommandText property has not been initialized
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.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized
Source Error:
Line 81:
Line 82: mySelectCmd.Connection.Open()
Line 83: mySelectCmd.ExecuteNonQuery()
Line 84: MyConnString.Close()
Line 85:
The pages code is below:
<%@ Page Language="VB" Debug="True" validateRequest="False"%>
<%@ Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<script runat="server">
Dim MyConnString As SqlConnection
Dim mySelectCmd As SqlCommand
Dim mySelectQuery As String
dim myExecuteQuery As String
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
ReadMyData()
end if
end sub
Public Sub ReadMyData()
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable
dt.Columns.Add(New DataColumn("id", GetType(Integer)))
dt.Columns.Add(New DataColumn("title", GetType(String)))
dt.Columns.Add(New DataColumn("text", GetType(String)))
dt.Columns.Add(New DataColumn("section_no", GetType(String)))
mySelectQuery = "select * from content_text"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
MyConnString.Open()
Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()
Try
While myReader.Read()
dr = dt.NewRow()
dr(0) = myReader("id")
dr(1) = myReader("title")
dr(2) = myReader("text")
dr(3) = myReader("section_no")
dt.Rows.Add(dr)
End While
Finally
myReader.Close()
MyConnString.Close()
End Try
Pubs.DataSource = DT
Pubs.Databind()
End Sub
Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = -1
ReadMyData()
End Sub
Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = CInt(e.Item.ItemIndex)
ReadMyData()
End Sub
Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
myExecuteQuery= "Update content_text Set title=@title, text=@text, section_no=@section_no where id=@id"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
dim e_id as integer
dim e_title, e_text, e_section_no as string
e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), TextBox).Text
mySelectCmd.Connection.Open()
mySelectCmd.ExecuteNonQuery()
MyConnString.Close()
pubs.EditItemIndex = -1
ReadMyData()
End Sub
</script>
<html>
<head>
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif; font-size:8pt}
-->
</style>
</head>
<body>
<form runat="server">
<asp:datagrid
id="Pubs"
GridLines="Both"
CssClass="style1"
DataKeyField="id"
Border="0"
CellPadding="2"
font-name="Arial"
font-size="9pt"
OnEditCommand="Pubs_Edit"
OnUpdateCommand="Pubs_Update"
OnCancelCommand="Pubs_Cancel"
Autogeneratecolumns="false"
Showfooter="true"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="8pt"
HeaderStyle-BackColor="#B7CEDF"
Font-Bold="True"
ItemStyle-Font-Name="Arial"
ItemStyle-Font-Size="8pt"
ItemStyle-VerticalAlign="top"
runat="server">
<columns>
<asp:templateColumn>
<HeaderTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left">Word</td>
<td width="15%" align="left">Title</td>
<td width="70%" align="left">Text</td>
<td width="10%" align="left">Section Number</td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>
<td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>
<td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</EditItemTemplate></asp:templateColumn>
</columns>
</asp:datagrid>
</form>
</body>
</html>
View 5 Replies
View Related
Jul 9, 2007
Hello all. I'm new to this forum and of course I found it because I have a problem I can't find the answer to.
I have full-text indexing on a couple of tables in my database. Periodically, maybe once every couple of weeks, I get an error on a webpage that runs a stored procedure searching these tables. The error is as follows:
"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Products' because it is not full-text indexed."
I'm running Classic ASP, and the stored procedures run thousands of times a day. As soon as I get the error email from the website containing the url that created the error, I immediately click on it, but by that time, the error has stopped and everything is running properly.
The timing of the error does not coorespond to any other processes. My transactionals run on the hour and the index is re-built at 12:45am, yet I just received the error again at 12:45pm (no, I checked the clocks and they are right).
I do not have replication running and I don't know where to look next.
Server:
SQL 2005 sp1
Windows 2003 Server
Any ideas would be greatly appreciated.
Russ
View 2 Replies
View Related
Nov 4, 2005
I tried the following select:
select * from patientprofile where contains (BillingNotes, '572576-02')
And got the errormessage:
Server: Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'patientprofile' because it is not full-text indexed.
It shows BillingNotes as a text column
Is there a differnet way I can search this note field for records that contain that text value (572576-02)
View 3 Replies
View Related
Nov 5, 2007
Is there a way I could achieve something like this.
freetext(FirstName+LastName,@Name) FirstName and LastName are two columns in my database table.
What I am trying here is allowing the user to type their first name , last name or both. and write a search which can find there record for all the three cases.
Please Advice.
View 1 Replies
View Related
Aug 7, 2007
I'm attempting to query the MS Index Server. I allow the users to supply the search text and when doing that it is best to pass the text into the query via a parameter to protect against SQL Injection attacks. However, for some reason I cannot get the FREETEXT predicate to work with a parameter. The FREETEXT predicate works fine when the query is concatenated together. I'd like an example of the FREETEXT predicate working with MS Index Server (Windows 2003).
The following line works just fine: cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, '" + searchText + "') order by rank desc "
I'd like the following lines to work:cmd.Parameters.AddWithValue("@SearchText", searchText)
cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, @SearchText) order by rank desc "
So now I'm wondering if the MS Index Server doesn't support the use of a parameter in this way or if I somehow managed to get the syntax wrong.
Thoughts?
View 2 Replies
View Related
Feb 22, 2000
I've been running Freetexts on ASP through IIS. The problem with this is that the CPU usage is very big.
I've wanted to use Stored Procedures on SQL 7.0 to produce results out of Freetext. But i've been having problems. Please help:
CREATE PROCEDURE sp_match
@asp_str varchar(50)
as
select * from employees_table where
freetext(*, @asp_str )
return
View 1 Replies
View Related
Aug 23, 2007
Hi,
I realised that I am not able to do a FREETEXT search on multiple table, example:SELECT * FROM [tStaffDir], [tStaffDir_ClientExp], [tStaffDir_CoreSpecs], [tStaffDir_GlobalExp], [tStaffDir_Lang], [tStaffDir_PrevEmp], [tStaffDir_TerEdu] WHERE FREETEXT(*, @Name) ORDER BY [Name]
Can I use FREETEXTTABLE instead? How do I go about doing it?
View 2 Replies
View Related
Dec 10, 2007
Hi
I was looking at this link : http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=792720&SiteID=17&pageid=0
I am using SQL SERVER 2005.
I have noiseeng.txt and noiseenu.txt.
I have stopped all the MSSQLSERVER, MSSQLAGENT,BROWSER, EXPRESS and FULL SEARCH.
Then I removed all the numbers from the noiseeng.txt and noiseenu.txt.
I restarted the services again.
I tried to run this query
DECLARE @SearchWord nvarchar(30);
SET @SearchWord = 'MS';
SELECT ProductDescription
FROM Products
WHERE FREETEXT(ProductDescription, @SearchWord);
I can't seem to get any records out and it won't pick the two that I am actually looking for.
In the Products table, it has several rows containing:
MS1234
MS_1234
MS-1234
MS 1234
MS/1234
I am trying to retrieve the above records using just 'MS', but it just doesn't work at all.
Any suggestions, help or examples please - would be most appreciated.
Thanks
Newbie
View 4 Replies
View Related
Sep 14, 1998
I installed SQL server 7.0 beta 3 and I have been experimenting with the full text search capabilities. The catalogs appear to build correctly, but when I issue a query against the full text database, isql/w always returns with `contains` is not a recognized function name. I am confident of my query syntax, since I can execute an identical query against a database without full text search enabled and I get an error stating that the target database does not have full text searching enabled. Has anybody else encountered this? I am hoping that I am missing something obvious/simple.
View 2 Replies
View Related
Apr 20, 2008
Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns. Is there a way to do this? My two colums are:title and description
can anyone give any code snippets?
View 1 Replies
View Related
Sep 12, 2007
I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.
select
b.rank,
a.ProductID,
a.ProductName,
a.Sequence,
a.ProductImage,
a.ItemID,
a.ItemName,
a.ManufacturerItemCode,
a.ItemImage,
a.ItemSourceID,
a.PackageID,
a.BrandID,
a.BrandName,
a.ManufacturerID,
a.ManufacturerName,
a.ProductCategoryID,
a.CategoryID,
a.CategoryName,
d.CustomerGroupName,
isnull(h.PackageDescription,a.ItemPKG) as PKG,
case g.StockStatus
when 1 then 'Yes'
when 0 then 'No'
else ''
end as StockStatus,
isnull(g.StandardUnitPrice,a.ListPrice) as Price,
isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource
from
dbo.vw_mcProductItem a
inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]
inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID
inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID
inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID
inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID
left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID
left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID
left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId
left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID
where
d.CustomerGroupID = @CustomerGroupID
and f.UserID = @UserID
and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)
View 1 Replies
View Related
Aug 11, 2007
Hi,
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
Any thoughts appreciated.
Thanks
View 3 Replies
View Related
Mar 20, 2008
Hi everyone. I'm developing a web search engine using asp and SQL Server 2000. I need to return records that matches with a string entered by users. In example, suppose my database to have this structure and it's filled like this:
ID NAME KEYWORDS
--- ------- -----------
AA025 NAME1 attached, atic, common, business, hotels
AA026 NAME2 headache, medicin, aspirins, heat, health
AA027 NAME3 at, services, music, electronics
suppose that user enters 'at'. By now, i'm using this pattern '%<input_text>%'. So in this example, pattern would be '%at%'. As i remarked in the fields above, the three fields matches, and that's not what i'm looking for. I want that the result of that query be just the last field, with 'AA027' ID.
Thanks in advance guys.
Cheers.
View 14 Replies
View Related
Jan 30, 2008
hi can any one help me to get the pattern of 70-441 exam.
i need information total number of question in the exam..
and will there be any simulation as 70-431 exam.
View 3 Replies
View Related
Sep 15, 2005
Say I have an object that can be created by some users and not others. And, I have a method something like - function createObject(userId as Integer, objectName as String). The method calls a stored procedure using the two input arguments as parameters.Now, what I am wondering is this; I am obviously going to check in my SP to ensure that the user has the rights to create the new object before inserting the new record, and there will be an output parameter to send the new record ID back to the method, but do Ia) have a separate output parameter to report on the success of the creation (e.g. @Err Int OUTPUT)b) use the existing output (used to send the new record ID back) and send an error code (e.g. if newRecordId = -9999)c) somehow try to cause a failure of the SP that can be caught in a try/catch blockThanks in advance,Martin
View 1 Replies
View Related
Mar 30, 2015
I've been trying to find and filter out data that can cause FOR XML to fail with the error:FOR XML could not serialize the data for node ?? because it contains a character (0x0006) which is not allowed in XML.I thought it would be simple, just identify the rows that don't match the XML spec URL...
However, the following doesn't work.
select fieldname
from tablename
where fieldname
like N'%[^'+nchar(0x9)+nchar(0xA)+nchar(0xD)+nchar(0x20)+N'-'+nchar(0xD7FF)+N']%'
Not only does this fail to find the offending rows, when I checked by inverted it by taking out the "^" it still returns no rows.When I reduced 0xD7FF down to 0xFF it returns data, but I cannot add the other range back in without all rows not matching.I've experimented with values and found 0x02E9 was the highest I could go without all values vanishing. Even then, numbers lower than that caused a large variation in the number of rows returned.My Field is NVarchar(100) in SQL_Latin1_General_CP1_CI_AS.
View 1 Replies
View Related
Aug 22, 2007
We have a collection of charts, some of which we use the gray scale for the palette (users only have B&W printers). We need to use the same patterns in other charts for consistency. The only option I see in Edit Chart Value-->Appearance-->Series Style-->Fill is Gradient. Is there any way to pick a fill pattern?
The pattern is the diagonal lines slanting down from left to right. It is the third column in the gray scale, so I have tried to add a dummy column as the first column, so that my column would have this pattern. However, the chart allows for space width wise for the first column, so there is a blank space on the chart. I have the Data Output checked to No.
Any other ideas, I would appreciate it!
View 2 Replies
View Related
Mar 12, 2008
Hi,
How can i find the 2nd comma position of a string, if i have multiple commas in my field. I need SQL Statement
Ex:
F1
Andy,David,Martin,
Sam,Dan,Philip,
I need the position should be
F1 F2
Andy,David,Martin, 11
Sam,Dan,Philip, 8
Thanks,
Mears
View 8 Replies
View Related
Jun 7, 2007
I have two simple tables:
CREATE TABLE summaries ( summaryId int identity, summary varchar)CREATE TABLE phrasesToSearchFor ( phrase varchar)
I want to do this:
SELECT summaryFROM summariesWHERE CONTAINS(summary, <any of the values in phrasesToSearchFor>)
I've tried every weird permutation of the query I can think of, but can't find a way to make it work. Can anybody help?
Bonus Question:
I've got a function dbo.getPhraseToSearchFor(userId) that returns a phrase depending on the value of userId. I want to get a result table of (userId, summary) where for every user, I use dbo.getPhraseToSearchFor(userId) to figure out what to search the full-text catalog for. The docs say you can use a scalar function anywhere a scalar value is expected, but apparently that doesn't apply in this case -- you can't do CONTAINS(summary, dbo.getPhraseToSearchFor(23))
View 1 Replies
View Related
Apr 30, 2015
I need a simple tSQL code that will produce an array sequence values that has gap with 2 or more consecutive values, which can vary - so 2 or 3 or 4 between sequence of number. For example, I want it to return a series with a gap or space of 2- So it should return 1, 2, 5, 6, 9, 10.. or a gap of 3 - 1,2 ,6,7,11,12.. etc I know a single gap is quite simple using a recursive cte, but I haven't been able to find how to get this kind of result.
View 14 Replies
View Related
Dec 11, 2007
hi
i have a table with a column which contains a large paragraph of data.
i need to search for a pattern within this text and update it.
for example the column contains 4096 char.
within this text, i search for a pattern"qwerty" and convert it to "asdfgh".
any ideas how this can be done?
i'm using sql server 2000
i have to write something like
UPDATE [DB].[dbo].[Table] SET [Column1] = '.............', WHERE ..........
any help will be appreciated.
View 4 Replies
View Related
Aug 20, 2014
DelimitedSplit8k and PatternSplitLoop seem to have potential, but I'm just plain stuck on some things:
1. DelimitedSplit8k: the delimiter split the folder paths, but the pattern can be within the strings that result.
2. PatternSplitLoop: I would have to cross apply 16 times and have an awful WHERE clause to determine which of the four strings matched first.
Unless I'm missing something. Short example is below.
WITH testctes (string, pattern) AS (
SELECT 'oh_look_at_this.thing.hishers_stuffmine.craftyours_protein', 'his first' UNION ALL
SELECT 'i.am.a._thing.hershis_thingsmine.refrigeratoryours_potato', 'hers first' UNION ALL
SELECT 'path_like.things_minehers.some_elsehis_garbageyours_sneakers', 'mine first' UNION ALL
SELECT 'more_stuff.yoursminehershis_falafel', 'yours first'
)
SELECT string, pattern, ca.item, ca.itemnumber
FROM testctes
CROSS APPLY [dbo].[PatternSplitLoop] (string, '%his%') ca
View 9 Replies
View Related
Mar 19, 2008
I've decalred this stored procedure with a simple where and like statement. The problem is I don't see any result.
Here is my code
create procedure sp_Select_ProfileNames
@NameSearch varchar(50)
as
select ProfileFirstName +' '+ ProfileLastName as ProfileName
from Profiles
where ProfileLastName like '%@NameSearch%';
When i change this line:
where ProfileLastName like '%@NameSearch%';
to
where ProfileLastName like '%Bil%';
I see names starting with Bil, but when i enter Bil as param, I don't get anything.
What's wrong here ?
View 2 Replies
View Related
Aug 31, 2005
I am trying to do a select statement with SQL 2000 for all recordscontaining any number of lowercase letters. I have tried the following2 statements and they both seem to match both lowercase and uppercaseletters.SELECT * FROM customers WHERE name LIKE '%[a-z]%'SELECT * FROM customers WHERE name LIKE'%[abcdefghijklmnopqrstuvwxyz]%'Can anybody help?
View 4 Replies
View Related
Jan 3, 2007
Hi,
I need to store a list of parameters in a database. Each parameter has a name, description, comment and a value. Easy so far.
However the values are of different types. Each individual parameter has a value which may be of type int, decimal, string, boolean, custom type etc.
Which table design pattern is most appropriate?
We have a heated in-house discussion and I need supporting arguments.
Options explored so far:
1) (De-)serializing the value to a string-type.
2) Adding a column for each type, using only one column at a time.
3) Adding extra value-tables, one table for each type.
The disadvantages for each option are obvious and the basis for our discussion.
Your help in this matter will be appreciated.
Regards, Tonn
View 5 Replies
View Related
Aug 25, 2006
I want to check for multiple patterns in a particular column.
For one pattern I can write e.g.
SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.
My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery
e.g. (SELECT '%'+name+'%' FROM <TableName>)
Any thoughts?
View 10 Replies
View Related
Aug 9, 2007
I need a table value function for extracting strings matching a particular pattern from a long string.
e.g. I have a table called cs_Posts, it has a column called FormattedBody, this value can be something like:
Code Snippet<p>this is the 1st photo <a href="http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPGhttp://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG">http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG</A< A>>" border="0" alt="" /></a></p>
<p>this is the 2nd photo<a href="http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPGhttp://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG">http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG</A< A>>" border="0" alt="" /></a></p>
When this long string is input, the table value function should 2 rows:
http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG
http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG
Any idea?
View 4 Replies
View Related
Oct 29, 2007
I have a table called MessageBoard. It has a column called Messages.
A user can type text including any html tags through a text area ans when he saves it by clicking a button, the content typed by the user is saved in the MessageBoard Table (in the Messages) column. So once saved, the html tags are kept intact. If I have to find and replace certain html tags, what kind of SQL Query I have to write?
For example I want to find all the <pre> </pre> tags and replace it with <p> </p> tags. How do I do this?
View 6 Replies
View Related
