Text search Problem - part 2
-
Hi all
say i got table coulmn with routing strings like this:
RoutingString
"A->B->F"
"G->T->C"
"F->P->V"
In another table i got coulmn with the routing points:
RoutingPoint
A
B
C
......
Now,the user can choose some points:A,D,G and i should show him all the routing string contain one of those points.
Well i tried do it with LIKE but this work only against one string.
Any Idea???
-
Can you explain a bit more?
Table DDL and a few rows of sample data and your desired output would be nice as well. (gives us something to work with)
/Kenneth
-
here is some code my friend:
CREATE TABLE Routing
(
RoutingString nvarchar(2000)
)
CREATE TABLE Points
(
RoutingPoint nvarchar(10)
)
INSERT INTO Routing(RoutingString) VALUES ('A->C->F')
INSERT INTO Routing(RoutingString) VALUES ('B->G->D')
INSERT INTO Routing(RoutingString) VALUES ('A->D->K')
INSERT INTO Routing(RoutingString) VALUES ('A->B->K')
INSERT INTO Points(RoutingPoint) VALUES ('A')
INSERT INTO Points(RoutingPoint) VALUES ('B')
INSERT INTO Points(RoutingPoint) VALUES ('C')
INSERT INTO Points(RoutingPoint) VALUES ('D')
INSERT INTO Points(RoutingPoint) VALUES ('F')
INSERT INTO Points(RoutingPoint) VALUES ('G')
INSERT INTO Points(RoutingPoint) VALUES ('K')
My desire is that the user will choose some points,lets say 'C' & 'K' and i will return him the routes that contain them:
'A->C->F','A->D->K' and 'A->B->K'.
-
Try to use PATINDEX for example
select RoutingString
from routing
where PATINDEX('%C%' , RoutingString) > 0 or PATINDEX('%K%' ,RoutingString) > 0
-
OK but the problem i don't know in advance which points the user will choose...
-
try this one
create function select_points(@points char(3)) returns @tmp_table TABLE (RoutingString char(2000))
as
begin
declare @point char(1)
while len(@points) > 1
begin
select @point = substring(@points,patindex('%,%',@points) - 1 ,1)
select @points = substring(@points,patindex('%,%',@points) + 1, len(@points))
insert into @tmp_table select RoutingString from Routing where RoutingString like '%'+@point+'%'
end
select @point = @points
insert into @tmp_table select RoutingString from Routing where RoutingString like '%'+@point+'%'
return
end
select * from dbo.select_points('C,K')
-
Thanks
-
If you have the points in a table you could try this:
CREATE TABLE Routing
(
RoutingString nvarchar(2000)
)
CREATE TABLE PointsChosen
(
Point nvarchar(10)
)
INSERT INTO Routing(RoutingString) VALUES ('A->C->F')
INSERT INTO Routing(RoutingString) VALUES ('B->G->D')
INSERT INTO Routing(RoutingString) VALUES ('A->D->K')
INSERT INTO Routing(RoutingString) VALUES ('A->B->K')
insert into PointsChosen select 'C'
insert into PointsChosen select 'K'
select distinct r.RoutingString
from Routing r cross join PointsChosen p
where r.RoutingString like '%' + p.Point + '%'
drop table Routing
drop table PointsChosen
go
-
U ROCK
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply