asked on

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

Vitor Montalvão

The IN keyword doesn't use wildcards.

Olaf Doschke

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ão

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.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Olaf Doschke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Allen Pitts

ASKER

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.

johnsone

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 Doschke

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.

Mark Wills

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