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
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 173
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 (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86188 Visits: 18154
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
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 173
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 (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138239 Visits: 22888
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
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 173
Amazing thank you very much
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86188 Visits: 18154
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
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
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-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46020 Visits: 5440
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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1541 Visits: 978
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.

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