February 6, 2018 at 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
February 6, 2018 at 6:08 am
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
February 6, 2018 at 6:54 am
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
February 6, 2018 at 6:58 am
craig.jenkins - Tuesday, February 6, 2018 5:58 AMGuys, 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? 🙂
I think you'll need to expand that out:
And (Servtext LIKE '%WAR%' OR Servtext LIKE '%WARTEC%',...)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 6, 2018 at 7:14 am
Amazing thank you very much
February 6, 2018 at 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
February 16, 2018 at 4:35 am
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 + '%'
February 21, 2018 at 7:20 am
John Mitchell-245523 - Tuesday, February 6, 2018 8:04 AMWITH 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 22, 2018 at 5:20 am
sgmunson - Wednesday, February 21, 2018 7:20 AMJohn Mitchell-245523 - Tuesday, February 6, 2018 8:04 AMWITH 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.
February 23, 2018 at 11:20 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply