object doesn't contain any of suffixes defined in user-defined table SQL

Question

guys!

I don't know how to select objects that do not contain suffixes. I declare input table of suffixes

  @suffixes  dbo.tvp_stringArray READONLY

And then I'm selecting my objects

SELECT [Object]
FROM [myUsers]
WHERE [Object] IS NOT LIKE (SELECT suffix FROM suffixes)

How to add '%' to the selected suffix?

The same way you'd append any other string? `... (SELECT '%' + suffix + '%' FROM ...` — Bridge, Sep 18 '15 at 10:41
But the IS NOT LIKE would not work. You'd have to use EXISTS or a LEFT JOIN / IS NULL — larsts, Sep 18 '15 at 11:12

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

try this :

SELECT [Object]
FROM [myUsers]
WHERE [Object] IS NOT LIKE (SELECT  Cast(suffix as nvarchar(1000)) + '%' FROM suffixes)

Ref link

edited May 23 '17 at 11:58

Community

1
1

answered Sep 18 '15 at 10:44

BhushanK

1,205
6
23
39

score 0 · Accepted Answer · answered Sep 18 '15 at 11:16

As others have pointed out, you can concatenate the % to the suffix when using the LIKE operator.

But to take into account the fact that you only want the objects that does not match any of the suffixes, assuming there are more than one, you could add NOT EXISTS like this:

SELECT *
FROM MyUsers 
WHERE NOT EXISTS (SELECT 1 FROM Suffixes WHERE MyUsers.Object LIKE '%' + suffix );

SQL Fiddle

object doesn't contain any of suffixes defined in user-defined table SQL

2 Answers2