sqlsqlitesplittext-extractionqsqlquery

How to extract a part of string in sql?

I have the below data table (name tabel1) and I have to extract the English part from every row, for example from row one Education Sector.

ID Name
1 PK:"";UK:"2. Education Sector";SP:"Sector Educativo"; GR:"2. Bildungssektor";FR:"2. Secteur de l/éducation";
2 UK:"3. Football: pitch/ground";SP:"3. Campo de fútbol"; GR:"3. Fußballplatz/Boden";NR:"3. fotballbane/bane";FR:"3. Terrain de football";
3 JP:""; GR:"1. Stadt: Hauptstadt/Hauptstadt"; SP:"1. Ciudad: ciudad principal/capital"; UK:"1. City: main city/capital"; FR:"1. Ville : ville principale/capitale"; NR:"1. By: hovedby/hovedstad"; IND:"";
4 AF:""; IND:""; GR:"4. Andere"; SP:"4. Otras"; FR:""; NR:"4. Andre"; FR:"4. Les autres"; UK:"4. Others"

I am Expecting result 1 this way but cannot solve it:

ID Name
1 2. Education Sector
2 3. Football: pitch/ground
3 1. City: main city/capital
4 4. Others

I am trying this way but it's not getting the expected result:

SELECT SUBSTRING(LEFT(name, CHARINDEX(';', name) + 1, 100)
FROM table1
WHERE CHARINDEX('\[', name) = "2. Education Sector" OR CHARINDEX('\[', name) = "3. Football: pitch/ground" OR CHARINDEX('\[', name) = "1. City: main city/capital" OR CHARINDEX('\[', name) = "4. Others";

And I am expecting result 2 this way but cannot solve it:

ID Name
1 Education Sector
2 Football: pitch/ground
3 City: main city/capital
4 Others

I am trying this way but its not getting my expected result:

SELECT SUBSTRING(LEFT(name, CHARINDEX(';', name) + 1, 100)
FROM table1
WHERE CHARINDEX('\[', name) = "Education Sector" OR CHARINDEX('\[', name) = "Football: pitch/ground" OR CHARINDEX('\[', name) = "City: main city/capital" OR CHARINDEX('\[', name) = "Others";

any suggestion?

Solution

  • Because you tagged sql-server I can offer the following simple method, assuming you're using both SQL Server and a fully supported version:

    I was going to delete this answer but I'll leave it here in case you can make use of it in SQLLite - I am not familiar with the product personally.

    select Id, s.[Name]
from t
cross apply (
  select Trim(Replace(Replace([value], '"',''), 'UK:','')) 
  from string_split(Name, ';')
  where [value] like '%UK:%'
)s([Name]);

    See a Demo Fiddle