September 20, 2012 at 2:59 am
here iam having a parameter declare @DATAS varchar(8000)='siva coffe shop orian super market '
and now i just splited this datas using a split function
like this
siva
coffe
shop
orian
super
market
and following these webstise
http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/
i made a index for a table
in that table there is a column BusinessName for that only i just made free text
i just tried like this
SELECT *
FROM Business s
CROSS APPLY dbo.Split(@DATAS,'')ds
WHERE
FREETEXT( s.BusinessName, ds.Items)
but i couldnt find the solution clearly
it has to make search like this
it has to search the each words in @datas as one by one like this
@DATAS varchar(8000)='siva coffe shop orian super market '
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'siva')
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'coffe')
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'shop')
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'orian'
just like loop iam trying
not like this
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, @DATAS)
is there any option to do like that
September 20, 2012 at 9:34 am
Can you try to explain a bit more clearly what you are trying to do? Take a look at the first link in my signature about best practices.
_______________________________________________________________
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/
September 21, 2012 at 7:51 am
here i wrote a split function to split the values in the parameter
@DATAS varchar(8000)='siva coffe shop orian super market '
select*from dbo.Split(@DATAS,'')ds
which will split hte datas like this
items
siva
coffe
shop
orian
super
market
which was strored in a temporary table @table
id items
1 siva
2 coffe
3 shop
4 orian
5 super
6 market
and now i want to check full text one by one
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'siva')
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'coffe')
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'shop')
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'orian')
just like loop iam trying
just making while loop an search text one by one from the table
September 21, 2012 at 8:23 am
I will try one more time. Post some ddl (create table scripts) and sample data (insert statements). Incomplete sentence fragments and disjointed explanations will not generate much activity. I suspect that what you are trying to do is VERY easy but without any details it is impossible to help you. Read the article at the first link in my signature and follow the advice found there. If you are unwilling or unable to do that nobody can help you. The decision is yours, put in some effort and post some useful details or continue to beat your head against the wall and be frustrated.
_______________________________________________________________
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/
September 21, 2012 at 8:51 am
As a note for formatting, please only highlight (select) the code when clicking the formatting options. Like this
select myCol
from MyTable
If you have notes or text, keep them outside the formatting codes.
September 21, 2012 at 8:53 am
Have you created a full text index on the table? I don't believe you can do this on a temporary table, so it might be on a permanent one.
If you are trying to get multiple searches, you can UNION them or use an OR clause, but it's not really clear what you want to query/search on.
September 21, 2012 at 9:26 am
hi
Steve Jones
i didnt created full text index in temporary table. i created full text index in the table Business
by following this link
http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/
September 21, 2012 at 11:02 am
here i table script and a sample data
@DATAS varchar(8000)='siva coffe shop orian super market '
insert into @
select*from dbo.Split(@DATAS,'')ds
declare @table table
(
id int identity(1,1)
items varchar(8000)
)
Insert into @table (items)
select items from [Split](@DATAS,'')
which was strored in a temporary table @table
id items
1 siva
2 coffe
3 shop
4 orian
5 super
6 market
create table Business
(
Businessidint constraint pk_Business_BUNSTID primary key,
BusinessNamevarchar(300),
ResponseDatedatetime,
ModifiedDatedatetime,
IsActivebit
)
insert into Business (Businessid,BusinessName,IsActive)
select 1,'siva complex',1 union all
select 2,' john furniture',1 union all
select 3,'oriental super market',1 union all
select 4,'plazza',1 union all
select 5,'serinform',1 union all
select 6,'orian super market',1 union all
select 7,'siva coffe shop',1
now i want to check full text one by one
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'siva')
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'coffe')
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'shop')
SELECT *
FROM Business s
WHERE
FREETEXT( s.BusinessName, 'orian')
just like loop iam trying
just making while loop an search text one by one from the table
September 21, 2012 at 12:59 pm
/vent begin
You have been trying to work on this for over a month with various half-hearted attempts strewn all over this site. You need to realize that we are volunteers around here. If you were paying me I would dig through the mess you posted and sort it out. However because this is a free service YOU have to do some of the work. The "ddl" you posted is missing information, has syntax errors and is incomplete. If I was handed this from one of my developers we would be having a talk with HR about focus and paying attention to details. Language barriers are no reason to get sloppy with the one language we can both speak, in fact that should put more importance on speaking clearly in sql.
/vent end
OK so now you want to find BusinessID's 1, 3, 6, 7 if I am correct????
Your ddl for Business was fine so let's use that. If I understand your requirement this will work. It is not the best for performance but given that you have to parse a parameter and a column I am going to assume that performance is not a huge priority. If my understanding of your requirements are clear we can make this faster.
declare @DATAS varchar(8000)='complex coffe shop orian super market'
select b.BusinessID, b.BusinessName
from Business b
outer apply dbo.DelimitedSplit8K(b.BusinessName,'') bs
cross join dbo.DelimitedSplit8K(@Datas,'') s
where charindex(s.item, b.BusinessName) > 0
group by b.BusinessID, b.BusinessName
I believe from other posts that you are using the above DelimitedSplit8k function. If not, or for anybody else who happens on this thread you can find the code in the article linked in my signature about splitting strings.
_______________________________________________________________
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/
September 21, 2012 at 1:12 pm
Sean Lange (9/21/2012)
/vent beginYou have been trying to work on this for over a month with various half-hearted attempts strewn all over this site. You need to realize that we are volunteers around here. If you were paying me I would dig through the mess you posted and sort it out. However because this is a free service YOU have to do some of the work. The "ddl" you posted is missing information, has syntax errors and is incomplete. If I was handed this from one of my developers we would be having a talk with HR about focus and paying attention to details. Language barriers are no reason to get sloppy with the one language we can both speak, in fact that should put more importance on speaking clearly in sql.
/vent end
OK so now you want to find BusinessID's 1, 3, 6, 7 if I am correct????
Your ddl for Business was fine so let's use that. If I understand your requirement this will work. It is not the best for performance but given that you have to parse a parameter and a column I am going to assume that performance is not a huge priority. If my understanding of your requirements are clear we can make this faster.
declare @DATAS varchar(8000)='complex coffe shop orian super market'
select b.BusinessID, b.BusinessName
from Business b
outer apply dbo.DelimitedSplit8K(b.BusinessName,'') bs
cross join dbo.DelimitedSplit8K(@Datas,'') s
where charindex(s.item, b.BusinessName) > 0
group by b.BusinessID, b.BusinessName
I believe from other posts that you are using the above DelimitedSplit8k function. If not, or for anybody else who happens on this thread you can find the code in the article linked in my signature about splitting strings.
Only problem I see is trying to split strings using the empty string ('') as a delimiter. Don't think that will work too well.
September 21, 2012 at 2:37 pm
Lynn Pettis (9/21/2012)
Sean Lange (9/21/2012)
/vent beginYou have been trying to work on this for over a month with various half-hearted attempts strewn all over this site. You need to realize that we are volunteers around here. If you were paying me I would dig through the mess you posted and sort it out. However because this is a free service YOU have to do some of the work. The "ddl" you posted is missing information, has syntax errors and is incomplete. If I was handed this from one of my developers we would be having a talk with HR about focus and paying attention to details. Language barriers are no reason to get sloppy with the one language we can both speak, in fact that should put more importance on speaking clearly in sql.
/vent end
OK so now you want to find BusinessID's 1, 3, 6, 7 if I am correct????
Your ddl for Business was fine so let's use that. If I understand your requirement this will work. It is not the best for performance but given that you have to parse a parameter and a column I am going to assume that performance is not a huge priority. If my understanding of your requirements are clear we can make this faster.
declare @DATAS varchar(8000)='complex coffe shop orian super market'
select b.BusinessID, b.BusinessName
from Business b
outer apply dbo.DelimitedSplit8K(b.BusinessName,'') bs
cross join dbo.DelimitedSplit8K(@Datas,'') s
where charindex(s.item, b.BusinessName) > 0
group by b.BusinessID, b.BusinessName
I believe from other posts that you are using the above DelimitedSplit8k function. If not, or for anybody else who happens on this thread you can find the code in the article linked in my signature about splitting strings.
Only problem I see is trying to split strings using the empty string ('') as a delimiter. Don't think that will work too well.
It does work but it is rather strange. :w00t:
_______________________________________________________________
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply