Conversion failed when converting the nvarchar value '55,201' to data type int

Question

I am trying to build query to get districts that are saved as string using SQL Server. Here is the table "FranchiseInfoes"

FranchiseInfoPK  | ClientName   | Email       |   DistrictsFK

      2            |    asasd   | [email protected]   |  20,30,10

How I can select all FranchiseInfoes with districts?

Here is my code

SELECT
    dbo.FranchiseInfoes.FranchiseInfoPK,
    dbo.FranchiseInfoes.ClientName,
    dbo.FranchiseInfoes.Email,
    dbo.District.DistrictName,
FROM dbo.FranchiseInfoes
LEFT JOIN dbo.District ON dbo.District.DistrictPK = dbo.FranchiseInfoes.DistrictsFK 

Answer 1

A 3rd table to normalize the data is probably your best bet, but if you can't do that:

If you're using SQL Server 2016 or later, you have access to the string_split function which can break out that '20,30,10' into a table. Use that in a cross apply to make 1 row for each value. Wrap that in a common table expression and join to the District table:

    with SplitFranchiseInfoes as (
       select FranchiseInfoes.FranchiseInfoPK
            , FranchiseInfoes.ClientName
            , FranchiseInfoes.Email
            , itvf.DistrictsFK
         from dbo.FranchiseInfoes
       cross apply (
          select value as DistrictsFK
            from string_split(FranchiseInfoes.DistrictsFK,',')
       ) itvf
    )
       select SplitFranchiseInfoes.FranchiseInfoPK
            , SplitFranchiseInfoes.ClientName
            , SplitFranchiseInfoes.Email
            , District.DistrictName
         from SplitFranchiseInfoes
   left join dbo.District
          on District.DistrictPK = SplitFranchiseInfoes.DistrictsFK;

If you don't have access to string_split, you could craft your own function to do it (I recommend looking up string splitting with a Tally or numbers table). But if you can't add a 3rd table for normalization, you probably can't add a Tally table and User-defined function to split strings either.