I have a table that stores RDF triples:
triples(triple_id, sub_id, pre_id, obj_id)
The method (I need to write) will receive an array of numbers which correspond to pre_id values. I want to select all sub_id values that have a corresponding pre_id for all the pre_ids in the array that is passed in.
E.g. if I had a single pre_id values passed in... lets call the value passed in preId, I would do:
select sub_id from triples where pre_id=preId;
However since I have mutliple pre_id values I want to keep iterating through the pre_id values and only keep the sub_id values corresponding to the "triples" records that have both.
E.g. image there are five records:
triples(1, 34,65,23)
triples(2, 31,35,28)
triples(3, 32,32,19)
triples(4, 12,65,28)
triples(5, 76,32,34)
If I pass in an array of pre_id values [65,32] then I want to select the first, third, fourth and fifth records.
What would I do for that?
This might not work with every database, but the keyword in
can do this:
select sub_id from triples where pre_id in (65, 32)
You take in the incoming data as a single string, split it by delimiter, and put it into an @Table and use a JOIN
, EXISTS
or a sub query
to get the rows back that you want.
Here is how to split a string into a @Table
T-SQL: Opposite to string concatenation - how to split string into multiple records
Fully working sample in SQL 2008
DROP FUNCTION dbo.Split
go
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO
Declare @Triples Table
(
triple_id Int,
sub_id VarChar (10),
pre_id VarChar (10),
obj_id VarChar (10)
)
INSERT INTO @Triples VALUES
(1, 34,65,23),
(2, 31,35,28),
(3, 32,32,19),
(4, 12,65,28),
(5, 76,32,34)
SELECT *
FROM @Triples Triples
INNER JOIN dbo.split (',', '65,32') as InputValues
ON Triples.pre_id = InputValues.S
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With