CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2882307
user2882307

Reputation:

Check if value is in different table

so I am new to SQL and I have little experience with it. So please excuse if this is really basic but I can't seem to find it after googling...

so I have a table which contains a serialnumber field. I have a single inputfield which contains comma seperated values with each one being a serialnumber. I just want to display the serialnumbers that are in that comma seperated string.

So I have a splitstring function that I can use which splits the string at the commas like this:

SELECT * FROM dbo.SplitString('sn1,sn2,sn3,sn4,sn5,sn6', ',')

this returns a table with each sn being a row.

So I would want something like this:

SELECT
    *
FROM
    tbl_serials AS sn
WHERE
    sn.serialnumber in dbo.SplitString('sn1,sn2,sn3,sn4,sn5,sn6', ',')

so this would display only the rows where the serialnumber is inside the string. But I don't know how to syntactily construct this.

Any help is appreciated

Upvotes: 2

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One method to do this is using join:

SELECT sn.*
FROM tbl_serials sn JOIN
     dbo.SplitString('sn1,sn2,sn3,sn4,sn5,sn6', ',') as ss(val)
     ON sn.serialnumber = ss.val;

With your route, you need a subquery:

SELECT sn.*
FROM tbl_serials sn JOIN
WHERE sn.serialnumber in (SELECT val
                          FROM dbo.SplitString('sn1,sn2,sn3,sn4,sn5,sn6', ',') as ss(val)
                         );

In both these cases, the as ss(val) assigns a table alias and a column alias to the value. Some versions of SplitString might return more than one value (such as a position). If you have such a version, then you need to include the extra return values as named columns.

Upvotes: 3

Related Questions