Looking for long case_ID numbers from a listing that left off the last few characters


      ticketnumber AS CRM_CASE_ID,
      CAST(LOWER(FI.accountid) AS VARCHAR (40)) AS CRM_GUID,
      FA.chs_crmid AS CRM_FRIENDLY,
      FI.createdbyname AS CRM_REQUESTOR_ID,
      FI.createdbyname AS CRM_REQUESTOR_NAME,
      casetypecode AS CRM_ACTION_CODE,
      accountidname AS CRM_NAME,
FROM CONCENTRA_MSCRM.dbo.FilteredIncident FI
JOIN CONCENTRA_MSCRM.dbo.FilteredAccount FA (NOLOCK) ON  FA.accountID = FI.accountID
where ticketnumber like 'CAS-1249760-N%'
order by CRM_CASE_ID

This works. But I have a list of twenty so this query was drafted.

      ticketnumber AS CRM_CASE_ID,
      CAST(LOWER(FI.accountid) AS VARCHAR (40)) AS CRM_GUID,
      FA.chs_crmid AS CRM_FRIENDLY,
      FI.createdbyname AS CRM_REQUESTOR_ID,
      FI.createdbyname AS CRM_REQUESTOR_NAME,
      casetypecode AS CRM_ACTION_CODE,
      accountidname AS CRM_NAME,
JOIN CONCENTRA_MSCRM.dbo.Account FA (NOLOCK) ON  FA.accountID = FI.accountID
where ticketnumber in
order by CRM_CASE_ID

The second query executes successfully but returns zero rows.

Have been told that the 'WHERE FieldName IN (list_of_values)'
will work in T-SQL just as it works in PL/SQL. Apparently not.

Will a different syntax make the
'WHERE FieldName IN (list_of_values)'
statement work?
If not, what is a good way of searching for a list of strings?


Allen in Dallas
Allen PittsBusiness analystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
The IN keyword doesn't use wildcards.
Olaf DoschkeSoftware DeveloperCommented:
The easiest would be storing the list of wildcard strings into a temp table or declare a table variable and then do an inner join with a LIKE join condition:

INNER JOIN #temptable ON FieldName LIKE #temptable.wildcardstring

Open in new window

Bye, Olaf.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not sure if it's the easiest thing to do, Olaf.
The time that you need to type the CREATE TABLE #temp and the INSERT INTO #temp commands, will be longer than just type the LIKE operators. Unless there's something that's escaping me.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
MS SQL doesn't support that.  You have to fall back on standard WHERE clause coding:

where ticketnumber LIKE 'CAS-1249760-N%'
or ticketnumber LIKE 'CAS-1249585-J8%'
or ticketnumber LIKE 'CAS-1249622-H%'
or ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
For a one time query you're right. Typically I'd expect a frontend is used to pick a list.
Then an inner join is an elegant way to have a query (even stored proc) you can "parameterize" by preparing the corresponding temp table.

By the way, a possible solution to create such a temp table is using a tally table stored proc, which takes in the list of comma separated ticket number starting wildcards.

Bye, Olaf.
Allen PittsBusiness analystAuthor Commented:
Thanks guys.
Took a class on SQL. Asked the teacher if the class would use PL/SQL or T-SQL. Instructor said "It doesn't make any difference SQL is SQL."  Wish I could find that guy now.
johnsoneSenior Oracle DBACommented:
I see PL/SQL mentioned in the original question.  I assume by that it is meant to reference Oracle.

Oracle PL/SQL is Oracle's procedure language used in procedures, functions, packages, triggers, etc.  This question is about SQL, not PL/SQL.  I believe that SQL Server is the same way, but not positive (Transact-SQL is the procedure language and this question is purely a SQL question).

The query posted in the original question would give you the same result in Oracle as it is doing in SQL Server.  Oracle wouldn't process wild cards in an IN list either.
Olaf DoschkeSoftware DeveloperCommented:
Yes, the problem is not IN nor the SQL dialect, it's wanting to use wildcards in a list.

With MS SQL Server 2016 you could use String_Split:

Declare @words as Table (Word varchar(10));

Insert Into @words Values ('Here'),('is'),('data'),('you'),

Select W.* from @words W 
inner Join String_Split('H%,Y%,A%',',') L 
on W.word Like L.value

Open in new window

In your case your ticket number wildcards list would go as a single string parameter of string_split and you had less cumbersome writing of individual LIKE clauses.
Besides if your ticket are indeed complete without the %, you can use IN, but using IN the % is no wildcard character anymore and thus is looked for in the ticket number.

Bye, Olaf.
Mark WillsTopic AdvisorCommented:
Thats good olaf :)

was going to suggest using TVC as a derived table
-- use Table Value Constructor as a derived table

;with cte as
(select findlist
 from (values ('CAS-1249760-N%'),
('CAS-1316232-T%')) TVC(findlist)
select * from cte
where 'CAS-1251582-F123' like findlist

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.