November 7, 2012 at 10:40 am
We have this procedure where the user can search with using wildcard words and we need to bring back the results.
Here is the what we have so far:
Declare @BillType varchar(3) = ''
,@BillNumber int = 0
,@AnalystID int = 0
,@SearchText varchar(MAX) = 'fee, attorney, highway'
,@SessionID int = 49
Declare @SearchList varchar(MAX), @Pos int, @SearchID varchar(20)
Declare @TempList table
(SearchID varchar(20))
Set @SearchList = LTRIM(RTRIM(@SearchText)) + ','
Set @Pos = CHARINDEX(',', @SearchList, 1)
IF REPLACE(@SearchList, ',', '') <> ''
Begin
WHILE @Pos > 0
Begin
Set @SearchID = LTRIM(RTRIM(Left(@SearchList, @Pos - 1 )))
If @SearchID <> ''
Begin
Insert into @TempList(SearchID) Values(@SearchID)
End
Set @SearchList = RIGHT(@SearchList, LEN(@SearchList) - @Pos)
Set @Pos = CHARINDEX(',', @SearchList, 1)
End
End
--Select * from @TempList
IF (@BillType != '' or @BillNumber != 0 or @AnalystID != 0 or @SearchText != '')
Begin
SELECT bv.BillID as BillID
,bv.VersionID as VersionID
,RTRIM(bm.BillType) AS BillType
,bm.BillNumber as BillNumber
,RTRIM(bm.BillType) + ' ' + CONVERT(varchar(4), bm.BillNumber) as BillString
,bv.LRNumber as LRNumber
,bv.LRSource as LRSource
,bv.LRDash as LRDash
,bv.LRVersion as LRVersion
,(SELECT RTRIM(emplastname) + ', ' + RTRIM(empfirstname) AS Expr1 FROM employees.dbo.tblEmployees
WHERE (employeeID = (SELECT AnalystID FROM HouseCoreData.dbo.BillVersions
WHERE (VersionId = bv.VersionID)))) AS Analyst
,0 as AnalystID
,RTRIM(ISNULL((Select emplastname From employees.dbo.tblEmployees Where employeeID = bs.EmployeeID), '[SPONSOR]')) as Sponsor
,bv.LongTitle as LongTitle
,rss.StatusName as SummaryStatus
,rss.StatusID as SummaryStatusID
,Convert(bit, (Case When cn.NoticeID is not null Then 1 Else 0 End)) as HasNoticeID
,Convert(bit, (Case When ba.CommitteeNumber is not null Then 1 Else 0 End)) as HasReferral
,st.SummaryText
FROM HouseCoreData.dbo.BillVersions AS bv
LEFT OUTER JOIN HouseCoreData.dbo.BillMaster AS bm ON bv.BillID = bm.BillID
Left Outer Join HouseCoreData.dbo.BillSponsor AS bs On bv.BillId = bs.BillID and bs.SponsorType = 'Sponsor'
Left Outer Join HouseCoreData.dbo.SummaryText As st On bv.VersionId = st.VersionID
Left Outer Join HouseCoreData.dbo.CommitteeNoticeBills cnb on bm.BillID = cnb.BillID
Left Outer Join HouseCoreData.dbo.CommitteeNotice cn on cnb.NoticeID = cn.NoticeID
Left Outer Join HouseCoreData.dbo.BillActivity ba on bm.BillID = ba.BillID and ba.ActivityCode = 10 and ba.CommitteeNumber not in (Select ID From HouseCoreData.dbo.Committees Where CommName like 'Rules%' or CommName like 'Fiscal%')
Left Outer Join HouseCoreData.dbo.refSummaryStatus rss on bv.SummaryStatus = rss.StatusID
Where bv.SessionID = @SessionID and (bm.BillType = @BillType or @BillType = '')
and (bm.BillNumber = @BillNumber or @BillNumber = 0) and (bv.AnalystID = @AnalystID or @AnalystID = 0)
and (st.SummaryText like (Select '%'+SearchID+'%' From @TempList) or @SearchText = '')
End
We can run it if there is only one searchid word, but when there are more than one, we can't seem to make this work.
How can we set the summarytext to use all the SearchID words within this select statement and is it even possible?
Thank you!!
November 7, 2012 at 10:48 am
Hi and welcome to SSC. It is very hard for somebody to understand your exact problem here because we have nothing to reference (tables, data, etc). It seems that part of your code here is performing a string split. You will find a MUCH faster version of that by taking a look at the link in my signature about splitting strings.
Back to your problem, if you can post some ddl (create table statements) and sample data (insert statements) along with a clear explanation of what you want to achieve, we can try to help.
Take a look at the first link in my signature for best practices when posting questions. Once we have all the stuff needed to help you code this we can knock it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2012 at 11:19 am
Thank you Sean. Sorry, that was vague now that I read through it. To get more of an understanding.
We are creating a temp table variable to hold the search words that the users can use. For example, if they want to search on 'Highway, Attorney, Fee' our temp table will split the words into a table.
Declare @BillType varchar(3) = ''
,@BillNumber int = 0
,@AnalystID int = 0
,@SearchText varchar(MAX) = 'fee, attorney, highway'
,@SessionID int = 49
Declare @SearchList varchar(MAX), @Pos int, @SearchID varchar(20)
Declare @TempList table
(SearchID varchar(20))
Set @SearchList = LTRIM(RTRIM(@SearchText)) + ','
Set @Pos = CHARINDEX(',', @SearchList, 1)
IF REPLACE(@SearchList, ',', '') <> ''
Begin
WHILE @Pos > 0
Begin
Set @SearchID = LTRIM(RTRIM(Left(@SearchList, @Pos - 1 )))
If @SearchID <> ''
Begin
Insert into @TempList(SearchID) Values(@SearchID)
End
Set @SearchList = RIGHT(@SearchList, LEN(@SearchList) - @Pos)
Set @Pos = CHARINDEX(',', @SearchList, 1)
End
End
--Select * from @TempList should get you the following results:
SEARCHID
fee
attorney
highway
These are the words we need to use to search through the select part of the procedure. I bolded the part that we can't seem to figure out:
Begin
SELECT bv.BillID as BillID
,bv.VersionID as VersionID
,RTRIM(bm.BillType) AS BillType
,bm.BillNumber as BillNumber
,RTRIM(bm.BillType) + ' ' + CONVERT(varchar(4), bm.BillNumber) as BillString
,bv.LRNumber as LRNumber
,bv.LRSource as LRSource
,bv.LRDash as LRDash
,bv.LRVersion as LRVersion
,(SELECT RTRIM(emplastname) + ', ' + RTRIM(empfirstname) AS Expr1 FROM employees.dbo.tblEmployees
WHERE (employeeID = (SELECT AnalystID FROM HouseCoreData.dbo.BillVersions
WHERE (VersionId = bv.VersionID)))) AS Analyst
,0 as AnalystID
,RTRIM(ISNULL((Select emplastname From employees.dbo.tblEmployees Where employeeID = bs.EmployeeID), '[SPONSOR]')) as Sponsor
,bv.LongTitle as LongTitle
,rss.StatusName as SummaryStatus
,rss.StatusID as SummaryStatusID
,Convert(bit, (Case When cn.NoticeID is not null Then 1 Else 0 End)) as HasNoticeID
,Convert(bit, (Case When ba.CommitteeNumber is not null Then 1 Else 0 End)) as HasReferral
,st.SummaryText
FROM HouseCoreData.dbo.BillVersions AS bv
LEFT OUTER JOIN HouseCoreData.dbo.BillMaster AS bm ON bv.BillID = bm.BillID
Left Outer Join HouseCoreData.dbo.BillSponsor AS bs On bv.BillId = bs.BillID and bs.SponsorType = 'Sponsor'
Left Outer Join HouseCoreData.dbo.SummaryText As st On bv.VersionId = st.VersionID
Left Outer Join HouseCoreData.dbo.CommitteeNoticeBills cnb on bm.BillID = cnb.BillID
Left Outer Join HouseCoreData.dbo.CommitteeNotice cn on cnb.NoticeID = cn.NoticeID
Left Outer Join HouseCoreData.dbo.BillActivity ba on bm.BillID = ba.BillID and ba.ActivityCode = 10 and ba.CommitteeNumber not in (Select ID From HouseCoreData.dbo.Committees Where CommName like 'Rules%' or CommName like 'Fiscal%')
Left Outer Join HouseCoreData.dbo.refSummaryStatus rss on bv.SummaryStatus = rss.StatusID
Where bv.SessionID = @SessionID and (bm.BillType = @BillType or @BillType = '')
and (bm.BillNumber = @BillNumber or @BillNumber = 0) and (bv.AnalystID = @AnalystID or @AnalystID = 0)
and (st.SummaryText like (Select '%'+SearchID+'%' From @TempList) or @SearchText = '') End
If we search on one word, for example 'FEE' then we get results (as expected cause we have the summary text searching with one word. We, however, need to search for the summarytext to include all the search words.
An example of the summarytext:
HB 1357 -- ALTERNATIVES-TO-ABORTION AGENCIES (Gatschenberger) COMMITTEE OF ORIGIN: Committee on Children and Families This bill specifies that the constitutions and laws of the United States and Missouri must be interpreted, construed, applied, and enforced to fully protect the rights of an alternatives-to- abortion agency and its officers, agents, employees and volunteers to freely assemble and to free...
I need to figure out how to search on the wildcard words through the summarytext.
Here is some sample data:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
summarytext varchar(max),
billid int,
);
INSERT INTO #mytable
(ID, summarytext, billid)
SELECT '3657','HB 1357 ALTERNATIVES-TO-ABORTION AGENCIES (Gatschenberger) COMMITTEE OF ORIGIN: Committee on Children and Families This bill specifies that the fee, attorney','91406' UNION ALL
SELECT '3658','HB 1357 -- ALTERNATIVES-TO-ABORTION AGENCIES (Gatschenberger) COMMITTEE OF ORIGIN: Committee on Children and Families This bill specifies that the highway','91406' UNION ALL
SELECT '3659','HB 1357 -- ALTERNATIVES-TO-ABORTION AGENCIES (Gatschenberger) COMMITTEE OF ORIGIN: Committee on Children and Families This bill specifies fee the constitutions','91406' UNION ALL
SELECT '3660','HB 1357 -- ALTERNATIVES-TO-ABORTION AGENCIES (Gatschenberger) COMMITTEE OF ORIGIN: Committee on Children and Families This bill specifies that the constitutions','91407'
Declare
@SearchText varchar(MAX) = 'fee, attorney'
Declare @SearchList varchar(MAX), @Pos int, @SearchID varchar(20)
Declare @TempList table
(SearchID varchar(20))
Set @SearchList = LTRIM(RTRIM(@SearchText)) + ','
Set @Pos = CHARINDEX(',', @SearchList, 1)
IF REPLACE(@SearchList, ',', '') <> ''
Begin
WHILE @Pos > 0
Begin
Set @SearchID = LTRIM(RTRIM(Left(@SearchList, @Pos - 1 )))
If @SearchID <> ''
Begin
Insert into @TempList(SearchID) Values(@SearchID)
End
Set @SearchList = RIGHT(@SearchList, LEN(@SearchList) - @Pos)
Set @Pos = CHARINDEX(',', @SearchList, 1)
End
End
Select * from @TempList
SELECT ID, billid from #mytable
where SummaryText like (Select '%'+SearchID+'%' From @TempList) or @SearchText = '')
This should return the first record only.
I hope this helps some.
November 7, 2012 at 12:52 pm
Thanks for the sample data and the stripped down version. Reading this again it is obvious that what you need is to read the article I suggested about splitting strings. Your code for splitting will work but it is not efficient. Read that article and make sure you understand it. In that article you will find code for a table valued function called DelimitedSplit8K. This function and the tally table that drives will change your mind in the way you look at data. The tally table has been referred to as the "Swiss Army knife of SQL". It can be used for so many things to turn looping logic into more efficient set based logic.
I rewrote your original query using the Delimited split function. This completely removes the need for all of that looping and splitting code. The only difference I made is that I removed the spaces in @SearchText. It will still work perfectly fine if you leave the spaces but your results will end up with a space on the left side.
Declare @BillType varchar(3) = ''
,@BillNumber int = 0
,@AnalystID int = 0
,@SearchText varchar(MAX) = 'fee,attorney,highway'
,@SessionID int = 49
SELECT bv.BillID as BillID
,bv.VersionID as VersionID
,RTRIM(bm.BillType) AS BillType
,bm.BillNumber as BillNumber
,RTRIM(bm.BillType) + ' ' + CONVERT(varchar(4), bm.BillNumber) as BillString
,bv.LRNumber as LRNumber
,bv.LRSource as LRSource
,bv.LRDash as LRDash
,bv.LRVersion as LRVersion
,(SELECT RTRIM(emplastname) + ', ' + RTRIM(empfirstname) AS Expr1 FROM employees.dbo.tblEmployees
WHERE (employeeID = (SELECT AnalystID FROM HouseCoreData.dbo.BillVersions
WHERE (VersionId = bv.VersionID)))) AS Analyst
,0 as AnalystID
,RTRIM(ISNULL((Select emplastname From employees.dbo.tblEmployees Where employeeID = bs.EmployeeID), '[SPONSOR]')) as Sponsor
,bv.LongTitle as LongTitle
,rss.StatusName as SummaryStatus
,rss.StatusID as SummaryStatusID
,Convert(bit, (Case When cn.NoticeID is not null Then 1 Else 0 End)) as HasNoticeID
,Convert(bit, (Case When ba.CommitteeNumber is not null Then 1 Else 0 End)) as HasReferral
,st.SummaryText
FROM HouseCoreData.dbo.BillVersions AS bv
LEFT OUTER JOIN HouseCoreData.dbo.BillMaster AS bm ON bv.BillID = bm.BillID
Left Outer Join HouseCoreData.dbo.BillSponsor AS bs On bv.BillId = bs.BillID and bs.SponsorType = 'Sponsor'
Left Outer Join HouseCoreData.dbo.SummaryText As st On bv.VersionId = st.VersionID
Left Outer Join HouseCoreData.dbo.CommitteeNoticeBills cnb on bm.BillID = cnb.BillID
Left Outer Join HouseCoreData.dbo.CommitteeNotice cn on cnb.NoticeID = cn.NoticeID
Left Outer Join HouseCoreData.dbo.BillActivity ba on bm.BillID = ba.BillID and ba.ActivityCode = 10 and ba.CommitteeNumber not in (Select ID From HouseCoreData.dbo.Committees Where CommName like 'Rules%' or CommName like 'Fiscal%')
Left Outer Join HouseCoreData.dbo.refSummaryStatus rss on bv.SummaryStatus = rss.StatusID
join dbo.DelimitedSplit8K(@searchText, ',') d on cte.SearchCol like '%' + d.Item + '%'
Where bv.SessionID = @SessionID and (bm.BillType = @BillType or @BillType = '')
and (bm.BillNumber = @BillNumber or @BillNumber = 0) and (bv.AnalystID = @AnalystID or @AnalystID = 0)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2012 at 1:45 pm
Thank you so much.
I'm reading the article and realize this is something that can help in our environment. I created the function and ran the query and it works to a point. And it may be that I'm missing something here. I'm still fairly new to SQL Server so I am not surprised if I am missing something.
First off, where is the cte.SearchCol identifier coming from?
I substituted that with st.SummaryText and got good results, but the join is acting as as or' and not an 'and' clause. So, I'm getting results with 'fee' or 'attorney' and I need the results with 'fee' and 'attorney' in the same summarytext record.
Does that make sense?
Thanks!!
November 7, 2012 at 1:52 pm
huestetwins (11/7/2012)
Thank you so much.I'm reading the article and realize this is something that can help in our environment. I created the function and ran the query and it works to a point. And it may be that I'm missing something here. I'm still fairly new to SQL Server so I am not surprised if I am missing something.
First off, where is the cte.SearchCol identifier coming from?
I substituted that with st.SummaryText and got good results, but the join is acting as as or' and not an 'and' clause. So, I'm getting results with 'fee' or 'attorney' and I need the results with 'fee' and 'attorney' in the same summarytext record.
Does that make sense?
Thanks!!
The cte.SearchCol is poor posting on my part. I tossed together a skeleton test prior to tweaking your code and forgot to change that to your search criteria. It sounds like you figured that out. My apologies for the confusion there.
That does make sense. Give me a few and I will work something up.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2012 at 2:31 pm
OK I used your #mytable as the basis for this. I removed the identity from ID so the inserts would work. 😉
CREATE TABLE #mytable
(
ID INT PRIMARY KEY CLUSTERED,
summarytext varchar(max),
billid int,
);
INSERT INTO #mytable
(ID, summarytext, billid)
SELECT '3657','HB 1357 ALTERNATIVES-TO-ABORTION AGENCIES (Gatschenberger) COMMITTEE OF ORIGIN: Committee on Children and Families This bill specifies that the fee, attorney','91406' UNION ALL
SELECT '3658','HB 1357 -- ALTERNATIVES-TO-ABORTION AGENCIES (Gatschenberger) COMMITTEE OF ORIGIN: Committee on Children and Families This bill specifies that the highway','91406' UNION ALL
SELECT '3659','HB 1357 -- ALTERNATIVES-TO-ABORTION AGENCIES (Gatschenberger) COMMITTEE OF ORIGIN: Committee on Children and Families This bill specifies fee the constitutions','91406' UNION ALL
SELECT '3660','HB 1357 -- ALTERNATIVES-TO-ABORTION AGENCIES (Gatschenberger) COMMITTEE OF ORIGIN: Committee on Children and Families This bill specifies that the constitutions','91407' union all
SELECT '3661','COMMITTEE OF ORIGIN: attorney and fee are reserved to demonstrate that order doesn''t matter constitutions','91408'
Declare @SearchText varchar(MAX) = 'fee, attorney'
select * from #mytable --just to see the entire table
select ID, summarytext, billid
from
(
select ID, summarytext, billid, row_NUmber() over(partition by summarytext order by newid()) as RowNum
from #mytable t
join dbo.DelimitedSplit8K(@SearchText, ',') d on t.summarytext like '%' + d.Item + '%'
) x
where RowNum = (select count(*) from dbo.DelimitedSplit8K(@SearchText, ','))
group by ID, summarytext, billid
drop table #mytable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2012 at 3:17 pm
Sean,
Thank you so so much, after tweaking it a bit, it works perfectly in our procedure. My developer is over the moon happy.
And the function is going to come in handy for many other projects.
Thank you! 😀
November 7, 2012 at 3:41 pm
huestetwins (11/7/2012)
Sean,Thank you so so much, after tweaking it a bit, it works perfectly in our procedure. My developer is over the moon happy.
And the function is going to come in handy for many other projects.
Thank you! 😀
You are very welcome. Make sure that you actually understand what that function is doing. Remember that at 3am when the production system comes crashing down it is your phone number your boss will be calling. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply