How can I modify a SQL query for take only some data from a column?-CodePudding

I have a SQL query that works fine:

SELECT Parti.producttopgroup,
    Parti.productgroup,
    Parti.productsubgroup,
    Parti.manufacturer,
    Costruttore.longname AS Costruttore,
    Parti.supplier,
    Fornitore.longname AS Fornitore,
    Parti.partnr,
    Parti.ordernr,
    Parti.description1,
    Parti.description2,
    Parti.description3,
    Parti.packagingquantity,
    Parti.quantityunit
FROM tblPart AS Parti
    LEFT OUTER JOIN tblAddress AS Costruttore 
        ON Parti.manufacturer = Costruttore.shortname
    LEFT OUTER JOIN tblAddress AS Fornitore 
        ON Parti.supplier = Fornitore.shortname

I realized that the data contained in the database in some columns are multilingual, something like :

"it_IT@Ciao;en_US@Hello;..."

I need to have my data only in one language at time, so I have tried to adjust my query with the TRIM and REPLACE stateament, but no luck. There's a way for going from this string: "it_IT@Ciao;en_US@Hello;..." to "Ciao"?

CodePudding user response：

First off, you should absolutely fix this design if you can, it's horrible.

Be that as it may, you can use STRING_SPLIT and SUBSTRING in a subquery

SELECT ...,
  YourColumn = (
    SELECT TOP (1) SUBSTRING(s.value, 7, LEN(s.value))
    FROM STRING_SPLIT(t.YourColumn, ';') s
    WHERE s.value LIKE 'en_US@%'
  )
FROM ...

If you just wanted the first one always then you can do

SELECT ...,
  YourColumn = SUBSTRING(LEFT(s.value, CHARINDEX(';', s.value) - 1), 7, LEN(s.value))
FROM ...