How to extract a part of string in sql?

December 7, 2022

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