SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


USE IN AND LIKE TOGETHER??


USE IN AND LIKE TOGETHER??

Author
Message
craig.jenkins
craig.jenkins
SSC Eights!
SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)

Group: General Forum Members
Points: 967 Visits: 254
Guys, is there a way to use the following but where the statement shows 'And Servtext IN ('WAR' ,'WARTEC' ,'TRIAGE' ,'LAB' ,'BDIAG')' change this to a like statement. Obliviously And Servtext LIKE (%'WAR' ,'WARTEC' ,'TRIAGE' ,'LAB' ,'BDIAG'%) is not correct?

Thanks

SELECT *
FROM Bookings
WHERE Created >= '2017-01-01'
AND Created < '2018-01-01'
AND DEALER in ('CA', 'BAA', 'BA')
AND Servtext IN ('WAR' ,'WARTEC' ,'TRIAGE' ,'LAB' ,'BDIAG')
ORDER BY ServCode
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)

Group: General Forum Members
Points: 133360 Visits: 19374
Not like that, but you could use a table value constructor and join to that. I think that would work. If you post up some table DDL (CREATE TABLE statement) and sample data (INSERT statement(s)), someone may give you a tested query.

AND ServText LIKE '%' + TVCText + '%'


John
craig.jenkins
craig.jenkins
SSC Eights!
SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)

Group: General Forum Members
Points: 967 Visits: 254
Thank you John,

Sample data below. Many thanks

-- Sample data
IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL DROP TABLE #SampleData
SELECT * INTO #SampleData FROM (VALUES
('BA', 'WARTEC/COMGEO'),
('BA', 'WARCAMP/COMGEO'),
('BA', 'WARCAMP/WARTEC/COMGEO'),
('BA', 'MILEAGE/CC/NOTES/CAM/BHC'),
('BA', 'NOTES/MILEAGE/AICAM/BHC'),
('BA', 'CAMPAIGN'),
('BA', 'LAB'),
('BA', 'CAMPAIGN/BDIAG'),
('BA', 'BDIAG'),
('BA', 'BDIAG'),
('BA', 'LAB/TRIAGE'),
('BA', 'MILEAGE/CC/NOTES/CAM'),
('BA', 'TRIAGE')

) d (Name, ref)

SELECT * FROM #SampleData
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217184 Visits: 24923
craig.jenkins - Tuesday, February 6, 2018 5:58 AM
Guys, is there a way to use the following but where the statement shows 'And Servtext IN ('WAR' ,'WARTEC' ,'TRIAGE' ,'LAB' ,'BDIAG')' change this to a like statement. Obliviously And Servtext LIKE (%'WAR' ,'WARTEC' ,'TRIAGE' ,'LAB' ,'BDIAG'%) is not correct?

Thanks

SELECT *
FROM Bookings
WHERE Created >= '2017-01-01'
AND Created < '2018-01-01'
AND DEALER in ('CA', 'BAA', 'BA')
AND Servtext IN ('WAR' ,'WARTEC' ,'TRIAGE' ,'LAB' ,'BDIAG')
ORDER BY ServCode

Obliviously, huh? Smile
I think you'll need to expand that out:
And (Servtext LIKE '%WAR%' OR Servtext LIKE '%WARTEC%',...)



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
craig.jenkins
craig.jenkins
SSC Eights!
SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)

Group: General Forum Members
Points: 967 Visits: 254
Amazing thank you very much
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)

Group: General Forum Members
Points: 133360 Visits: 19374
WITH MyValues AS (SELECT v FROM (VALUES('WAR') ,('WARTEC') ,('TRIAGE') ,('LAB') ,('BDIAG')) x(v) )
SELECT m.v, s.Name, s.ref
FROM #SampleData s
JOIN MyValues m ON s.ref LIKE '%' + m.v + '%'


John
Spandan Chowdhury
Spandan Chowdhury
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 19
Even though there is not one straight forward way to do it, here are multiple ways you can rewrite it:
1. Split out the direct matches to two parts and design your where clause as:
 
SELECT *
FROM Table
WHERE ([columnName] IN ('A','B','C') OR [columnName] LIKE '%P% OR [columnName] LIKE '%Q%)


2. Option 2:
WITH matching AS (SELECT [str] FROM (VALUES('P') ,('Q') ,('R')) x(str) )
SELECT m.[str], s.[columnName], ....
FROM Table t
JOIN matching m ON t.[columnName] LIKE '%' + m.v + '%'



sgmunson
sgmunson
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95814 Visits: 7193
John Mitchell-245523 - Tuesday, February 6, 2018 8:04 AM
WITH MyValues AS (SELECT v FROM (VALUES('WAR') ,('WARTEC') ,('TRIAGE') ,('LAB') ,('BDIAG')) x(v) )
SELECT m.v, s.Name, s.ref
FROM #SampleData s
JOIN MyValues m ON s.ref LIKE '%' + m.v + '%'


John


Don't forget that this raises the possibility that a given value might match more than one of the rows in the CTE, and thus you might see multiple rows of output for only one row in the source table. If you can avoid including m.v in the select list, you can use SELECT DISTINCT to solve that problem.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
gvoshol 73146
gvoshol 73146
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2552 Visits: 1337
sgmunson - Wednesday, February 21, 2018 7:20 AM
John Mitchell-245523 - Tuesday, February 6, 2018 8:04 AM
WITH MyValues AS (SELECT v FROM (VALUES('WAR') ,('WARTEC') ,('TRIAGE') ,('LAB') ,('BDIAG')) x(v) )
SELECT m.v, s.Name, s.ref
FROM #SampleData s
JOIN MyValues m ON s.ref LIKE '%' + m.v + '%'


John


Don't forget that this raises the possibility that a given value might match more than one of the rows in the CTE, and thus you might see multiple rows of output for only one row in the source table. If you can avoid including m.v in the select list, you can use SELECT DISTINCT to solve that problem.

Might?
Note that LIKE %WARTEC% is redundant to LIKE %WAR% - so the poster's first line of sample data will be returned twice.

Jason A. Long
Jason A. Long
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19885 Visits: 7905
If you use the following, you can control, at the value level, where and how the wildcards are applied...

SELECT
sd.Name,
sd.ref
FROM
#SampleData sd
CROSS APPLY ( VALUES ('%WAR'),('WARTEC'),('%TRIAGE%'),('LAB'),('BDIAG%') ) pv (predicate_values)
WHERE
sd.ref LIKE pv.predicate_values;


so...

IF OBJECT_ID('tempdb..#SampleData') IS NOT NULL
DROP TABLE #SampleData;
SELECT
d.Name,
d.ref
INTO #SampleData
FROM ( VALUES
('BA', 'WARTEC/COMGEO'),
('BA', 'WARCAMP/COMGEO'),
('BA', 'WARCAMP/WARTEC/COMGEO'),
('BA', 'MILEAGE/CC/NOTES/CAM/BHC'),
('BA', 'NOTES/MILEAGE/AICAM/BHC'),
('BA', 'CAMPAIGN'),
('BA', 'LAB'),
('BA', 'CAMPAIGN/BDIAG'),
('BA', 'BDIAG'),
('BA', 'BDIAGXXXX'),
('BA', 'LAB/TRIAGE'),
('BA', 'MILEAGE/CC/NOTES/CAM'),
('BA', 'TRIAGE'),
('BA', 'SOMEWAR')
) d (Name, ref);

SELECT
sd.Name,
sd.ref
FROM
#SampleData sd
CROSS APPLY ( VALUES ('%WAR'),('WARTEC'),('%TRIAGE%'),('LAB'),('BDIAG%') ) pv (predicate_values)
WHERE
sd.ref LIKE pv.predicate_values;


returns...
Name ref
---- ------------------------
BA LAB
BA BDIAG
BA BDIAGXXXX
BA LAB/TRIAGE
BA TRIAGE
BA SOMEWAR

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search