How to extract a part of string in sql?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

You can try a combination of LEFT and RIGHT.

Find 'UK:"' in Name column
Take everything starting with 'UK:"' using RIGHT and LEN
Find first '"' in the remaining string - this is your last character position
Take everything up to the fist '"' - this is your result

SELECT  ID,
        LEFT
        (
              RIGHT(Name, LEN(Name) - CHARINDEX('UK:"', Name) - 3)
             ,CHARINDEX(RIGHT(Name, LEN(Name) - CHARINDEX('UK:"', Name) - 3), '"') - 1 -- subtracting 3 to remove "UK:"; then finding closing quotation mark
         )
FROM table1

Please note that this solution assumes that the structure of the string is always the same: starts with 'UK:"' and finishes with '"'.

CodePudding user response：

Here's how I would do it:

SELECT ID , SUBSTRING(name, CHARINDEX('UK', name) 4, CHARINDEX('"', name,CHARINDEX('UK', name) 4)-CHARINDEX('UK', name)-4) FROM table1

CodePudding user response：

This method uses a recursive call to split the string on the delimiter, then selects from that. Adapted from this post.

The first 'with' (Common table expression) just sets up the test data (used here like a temp table). The second called split parses the string on the semi-colon resulting in a CTE of rows consisting of ids and parsed string elements. Uncomment the query following that to see. Then, the final query selects the rows that start with 'UK' and returns the meat.

with tbl(id, str) as (
  select 1, 'PK:"";UK:"2. Education Sector";SP:"Sector Educativo"; GR:"2. Bildungssektor";FR:"2. Secteur de l/éducation";'
  union ALL
  select 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";'
  union ALL
  select 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:"";'
  union all
  select 4, 'AF:""; IND:""; GR:"4. Andere"; SP:"4. Otras"; FR:""; NR:"4. Andre"; FR:"4. Les autres"; UK:"4. Others"'
  union ALL
  select 5, 'AF:""; IND:""; GR:"4. Andere"; SP:"4. Otras"; FR:""; NR:"4. Andre"; FR:"4. Les autres";'
  ),
split(id, word, csv) AS (
  SELECT 
    id, '', str ||';'
  from tbl
  UNION ALL 
  SELECT id,
    trim(substr(csv, 0, instr(csv, ';'))), 
    substr(csv, instr(csv, ';')   1) 
  FROM split 
  WHERE csv != '' 
) 
-- Show splitting on delimiter
--SELECT id, word FROM split 
--WHERE word != ''
--order by id; 
SELECT id, substr(word, 5, length(word)-5) as UK_Result
FROM split 
WHERE word != ''
and word like '%UK%'
order by id; 

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