By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T
([Exchange] varchar(6), [Ticker] varchar(4));
INSERT INTO T
([Exchange], [Ticker])
VALUES
('NASDAQ', 'GOOG'),
('NASDAQ', 'APPL'),
('NASDAQ', 'MSFT'),
('NYSE', 'IBM'),
('NASDAQ', 'AMZN'),
('NYSE', 'FB');
6 rows affected
DECLARE @searchStr VARCHAR(500) = 'NYSE';
SELECT *
FROM T
WHERE
(
CASE WHEN CHARINDEX(',', @searchStr) > 0
THEN LEFT(@searchStr, CHARINDEX(',', @searchStr) - 1)
ELSE @searchStr
END = T. Exchange
)
AND
(
(T.Ticker IS NOT NULL AND CHARINDEX(',', @searchStr) = 0)
OR
(
CASE WHEN CHARINDEX(',', @searchStr) > 0
THEN RIGHT(@searchStr, CHARINDEX(',', @searchStr)-3)
END = T.Ticker
)
)
| Exchange | Ticker |
|---|---|
| NYSE | IBM |
| NYSE | FB |
DECLARE @searchStr VARCHAR(500) = 'NASDAQ,GOOG';
SELECT *
FROM T
WHERE
(
CASE WHEN CHARINDEX(',', @searchStr) > 0
THEN LEFT(@searchStr, CHARINDEX(',', @searchStr) - 1)
ELSE @searchStr
END = T. Exchange
)
AND
(
(T.Ticker IS NOT NULL AND CHARINDEX(',', @searchStr) = 0)
OR
(
CASE WHEN CHARINDEX(',', @searchStr) > 0
THEN RIGHT(@searchStr, CHARINDEX(',', @searchStr)-3)
END = T.Ticker
)
)
| Exchange | Ticker |
|---|---|
| NASDAQ | GOOG |