SQL get string after second comma-CodePudding

I have string like this:

'Ing. , Jožko, Mrkvička, Bc.'

and I need just:

'Mrkvička'

Thank you.

CodePudding user response：

Not the cleanest solution. But it can be done like this

 SELECT SUBSTRING_INDEX (SUBSTRING_INDEX("Ing. , Jožko, Mrkvička, Bc.", ',', -2),',',1)

It will give you your desired output

CodePudding user response：

in mysql:

select substring_index(substring_index('Ing. , Jožko, Mrkvička, Bc.', ',', 3), ',', -1)

CodePudding user response：

I would prefer doing a regex as below

select regexp_matches('Ing. , Jožko, Mrkvička, Bc.', '[^,] ,[^,] \s ,([^,] ),') 
from yourTable

Input :

'Ing. , Jožko, Mrkvička, Bc.'

Output :

'Mrkvička'

CodePudding user response：

You can try with the following query:

SELECT TOP 1 TRIM(value)
FROM STRING_SPLIT('Ing. , Jožko, Mrkvička, Bc.', ',')
ORDER BY CASE WHEN ROW_NUMBER() OVER(ORDER BY (SELECT 1)) = 3 
              THEN 0 
              ELSE 1 END

The STRING_SPLIT function will split your string on the delimiter of your choice (,), then the results are ordered on the rank using the ROW_NUMBER window function and limited at only one result with TOP 1.

Try it here.

This solution works for SQL Server.

CodePudding user response：

For PostgreSQL:

select regexp_matches('Ing. , Jožko, Mrkvička, Bc.', '[^,] ,[^,] ,\s ([^,] ),')