SQL Server T-SQL LIKE vs IN

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

Tried

SELECT
      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,
      CAST(LOWER(Incidentid) AS VARCHAR (40)) AS CRM_CASE_GUID
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.

SELECT
      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,
      CAST(LOWER(Incidentid) AS VARCHAR (40)) AS CRM_CASE_GUID
FROM CONCENTRA_MSCRM.dbo.Incident FI
JOIN CONCENTRA_MSCRM.dbo.Account FA (NOLOCK) ON  FA.accountID = FI.accountID
where ticketnumber in
('CAS-1249760-N%',
'CAS-1249585-J8%',
'CAS-1249622-H%',
'CAS-1249680-D%',
'CAS-1249684-Y%',
'CAS-1249807-N%',
'CAS-1251582-F%',
'CAS-1310991-T%',
'CAS-1311905-V%',
'CAS-1312034-Q%',
'CAS-1312081-J9%',
'CAS-1314106-Z%',
'CAS-1314447-Q%',
'CAS-1315555-Z%',
'CAS-1315829-N%',
'CAS-1310869-B%',
'CAS-1316092-W%',
'CAS-1316123-J7%',
'CAS-1316232-T%')
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?

Thanks.

Allen in Dallas
Allen PittsBusiness analystAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior 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 ...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The IN keyword doesn't use wildcards.
1
 
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.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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.
0
 
Olaf DoschkeConnect With a Mentor Software 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.
2
 
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.
0
 
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.
0
 
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'),
('are'),('only'),('partially'),('interested'),('in');

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.
2
 
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-1249585-J8%'),
('CAS-1249622-H%'), 
('CAS-1249680-D%'), 
('CAS-1249684-Y%'), 
('CAS-1249807-N%'), 
('CAS-1251582-F%'), 
('CAS-1310991-T%'), 
('CAS-1311905-V%'), 
('CAS-1312034-Q%'), 
('CAS-1312081-J9%'),
('CAS-1314106-Z%'),
('CAS-1314447-Q%'),
('CAS-1315555-Z%'),
('CAS-1315829-N%'), 
('CAS-1310869-B%'), 
('CAS-1316092-W%'), 
('CAS-1316123-J7%'),
('CAS-1316232-T%')) TVC(findlist)
)
select * from cte
where 'CAS-1251582-F123' like findlist

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.