Geek Answers Handbook

TSQL Data search in a filled comma string

I have a request that is not in any way. This is quite dynamic, and the way it is because of use in my application. In short, it allows the user to select the fields that they want to see in the report, and also to determine the logic (WHERE clause) and, finally, what order they want to sort.

To make this easier, I created an idea of ​​all the data that could be included in the report. Thus, he simply selects the fields that the user wants and requests a view.

I came across an instance where, instead of individual data points, I have several situations 1:many. An example is the list of affected countries. In my opinion, this is a comma separated list United States, United Kingdom, Etc. This data is simply populated and separated by commas in the creation of the views.

Question

When creating this, I intended to simply use a statement INfor the data. Then I realized that it INis only a table function and cannot be used for a data row.

Is there a way to take a value and compare it with a comma-separated data string?

I tried to create a SQL script, but the site crashed all the time.

CREATE TABLE Test (country varchar(100), account INT);
INSERT INTO Test ('United States, United Kingdom', '123');
INSERT INTO Test ('United States', '123567');
INSERT INTO Test ('United States, China, Japan', '123567');

-- Trying to find all the rows where `United States` is in the country column.

I tried to use LIKE %country%, but it doesn’t work the same way as INsince I return data that I don’t need if part of the country is in a different country name.

- , , IN, , ?

+4
2

- :

SELECT * FROM Test
WHERE ' ' + country + ',' LIKE '% United States,%'

, .

@BenJaspers, , :

SELECT * FROM Test
WHERE ',' + country + ',' LIKE '%, United States,%'
   OR ',' + country + ',' LIKE '%,United States,%'
+4

:

CREATE TABLE #Test (country varchar(100), account INT);
INSERT INTO #Test VALUES('United States, United Kingdom', '123');
INSERT INTO #Test VALUES('United States', '123567');
INSERT INTO #Test VALUES('United States, China, Japan', '123567');
INSERT INTO #Test VALUES('Ireland, United States, Japan', '123567');
INSERT INTO #Test VALUES('Northern Ireland, China, Japan', '123567');

DECLARE @SearchCountry VARCHAR(100)='Ireland';

WITH MyCTE AS
(
    SELECT account 
          ,country
          --The string will be splitted at its commas and is returned as XML in parts
          ,CAST('<x>' + REPLACE(country,', ','</x><x>') + '</x>' AS XML) AS CountrySplitted
    FROM #Test
)

SELECT * 
FROM MyCTE
--the first part of the xml is evaluated (may be with LIKE too)
WHERE CountrySplitted.exist('/x[text()=sql:variable("@SearchCountry")]')=1

DROP TABLE #Test;
+3

More articles:


All Articles